SQL Append Table Help (1 Viewer)

[Zippersabrat]

I have a db for our accounts receivables (clients that owe us money). The parent table holds the clients info (address, phone, etc.). One of the child tables [Contact Notes] holds all the contact info...relationship is set up as one-to-many. I am just getting started with VB and am creating a button on the main Client Info form that has as a subform Contact Notes. The button opens up a MsgBox asking if you are sure you want to send the client to collections...a bunch of stuff happens. My issue is in the following code:

Dim mySQL As String
mySQL = "INSERT INTO [Contact Notes] ( [Client ID], [Date of Contact], [Type of Contact], Notes )"
mySQL = mySQL & " SELECT Me.[Client ID] AS [Client ID], Date() AS [Date], 'Collections' AS Type, 'Sent' AS Notes;"

DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True

The idea is to add a record to [contact notes] with the client ID, current date, sets the contact type as Collections, and adds a note "sent". I want it to automatically retrieve the client ID from the form before it does the append to the table. Right now it pops up a box asking for the client ID but I know there must be away to make this happen automatically.

Can someone help?

Thanks!

 

[BobMcClellan]

change this: mySQL = mySQL & " SELECT Me.[Client ID] AS [Client ID], Date() AS [Date], 'Collections' AS Type, 'Sent' AS Notes;"

to this: mySQL = mySQL & " SELECT " & Me.[Client ID] & ", Date() AS [Date], 'Collections' AS Type, 'Sent' AS Notes;"

Me.[Client ID] was being seen as part of the string.

hth,
bob.

 

[Zippersabrat]

Woohoo! That worked like a charm! Thanks!