[AccessD] (no subject)

Hi Vlad
Many thanks this is cool

Have a great day

Darren



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of ACTEBS
Sent: Monday, 6 September 2004 2:31 PM
To: Access Developers discussion and problem solving
Subject: RE: [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
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com