[AccessD] (no subject)

Darren,

I wouldn't set it up like this. Try something like this:

    Dim dbs As dao.Database
    Dim rstDataToAppend As dao.Recordset
    Dim rstTableToAppendTo As dao.Recordset
    Dim strSQLDataForAppend As String
    
    strSQLDataForAppend = "SELECT .....Whatever...."

    
    Set dbs = CurrentDB()
    Set rstDataToAppend = dbs.OpenRecordset(strSQLDataForAppend,
dbOpenDynaset)
    Set rstTableToAppendTo = dbs.OpenRecordset("tblYourTableToAppendTo",
dbOpenDynaset)
    
    Do Until rstDataToAppend.EOF
            With rstTableToAppendTo
                .AddNew

                	!Feild1FromTable =
rstDataToAppend!Feild1FromRecordset
                	!Feild2FromTable =
rstDataToAppend!Feild2FromRecordset
			etc etc etc....

                .Update
            End With
        rstDataToAppend.MoveNext
    Loop
    
    rstDataToAppend.Close
    rstTableToAppendTo.Close
    Set rstDataToAppend = Nothing
    Set rstTableToAppendTo = Nothing
    dbs.close
    Set dbs = Nothing

A good idea is to also check whether the recordset has any data before
hand, otheriwse this will throw up an error. Something like:

If rstDataToAppend.Recordcount > 0 then
	Do Until rstDataToAppend.....etc etc etc

HTH
Regards
Vlad


Web: www.actebs.com.au
 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren DICK
Sent: Monday, 6 September 2004 1:36 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] (no subject)


Hello all
I'm having a little trouble with the syntax of an append SQL string

I am setting a string value in code based on a booking ID 
I want to iterate through all the bookings - get the bookingID and then
Append it to a table.

Pretty basic, but I can never get the correct 'encasing' of the 
coded value right. EG where to use quotes, where to use ampersands etc.
The code snip below is all cool except for the bit in the SQL where it
is 
putting the coded value into the relevant field in the table, IE after
the SELECT portion 
of the SQL

Can anyone tidy this up for me?

Many thanks in advance

	
Dim strMyValueAsAString as string

	strMyValueAsAString = Me.BookingID

            appSQL = "INSERT INTO tblMyBookings (
BookingIDFromBookingsTable
) " _
            & "SELECT " & strMyValueAsAString & " FROM tblMyBookings ;"
            
Have a great day

Darren


-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com