DoCmd.RunSQL "INSERT INTO (append)

[Wayne-I-M]

Hi

I am looking to append a new record to a joining table (TblBookings)

TblClients (Primary = ClientID)
TblTrip (Primary = TripID)

tblBookings
BookingID
ClientID
TripID


This does not work :-(

Private Sub ButUpdateBooking_Click()
'save record'
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'this is section that causes the problem'
DoCmd.RunSQL "INSERT INTO [tblbookings] Set [TripID] =" & Me.TxTTripID
DoCmd.RunSQL "INSERT INTO [tblbookings] Set [ClientID] =" & Me.txtClientID

'other form requery'
Forms![frmAddNewMember]![frmAddNewMember_List].Form![MembersList].Requery

End Sub


the 2 unbound boxes (starting txt) are filled with other code and work fine.

Obviously the INSERT INTO is not working - any ideas please

Thanks for any tips

 

[Jeff Boyce]

Wayne

I'm going to guess that you want to create ONE record in the Booking table,
with ClientID and TripID. Your code would insert two records. To insert
more than one field, take a look at Access HELP for the syntax of "INSERT
INTO"

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Hans Up]

'this is section that causes the problem'
DoCmd.RunSQL "INSERT INTO [tblbookings] Set [TripID] =" & Me.TxTTripID
DoCmd.RunSQL "INSERT INTO [tblbookings] Set [ClientID] =" & Me.txtClientID

Are confusing INSERT and UPDATE statement syntax?

Single-record append query:

INSERT INTO target [(field1[, field2[, …]])] VALUES (value1[,
value2[, …])

 

[Wayne-I-M]

Hi Jeff

Thanks for the advice I use this which seems to do the trip after messing
around with it a litte

CurrentDb.Execute "INSERT INTO tblBookings (ClientID,TripID) VALUES (" &
txtClientID.Value & "," & TxTTripID.Value & ")"

Thanks again
--
Wayne
Manchester, England.

Wayne

I'm going to guess that you want to create ONE record in the Booking table,
with ClientID and TripID. Your code would insert two records. To insert
more than one field, take a look at Access HELP for the syntax of "INSERT
INTO"

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi

I am looking to append a new record to a joining table (TblBookings)

TblClients (Primary = ClientID)
TblTrip (Primary = TripID)

tblBookings
BookingID
ClientID
TripID


This does not work :-(

Private Sub ButUpdateBooking_Click()
'save record'
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'this is section that causes the problem'
DoCmd.RunSQL "INSERT INTO [tblbookings] Set [TripID] =" & Me.TxTTripID
DoCmd.RunSQL "INSERT INTO [tblbookings] Set [ClientID] =" & Me.txtClientID

'other form requery'
Forms![frmAddNewMember]![frmAddNewMember_List].Form![MembersList].Requery

End Sub


the 2 unbound boxes (starting txt) are filled with other code and work
fine.

Obviously the INSERT INTO is not working - any ideas please

Thanks for any tips

 

[John Spencer]

Perhaps what you want is the following string to insert ONE record into the
table based on txtTripID and txtClientID

"INSERT INTO tblBookings (TripID, ClientID) VALUES (" & me.txtTripID & ", " &
Me.txtClientID & ")"

That assumes that TripID and ClientID are NUMERIC fields. IF they are text
fields the you need to include the text delimiters [quote marks = Chr(34)] in
the string.

"INSERT INTO tblBookings (TripID, ClientID) VALUES (" & Chr(34) & me.txtTripID
& Chr(34) & ", " & Chr(34) & Me.txtClientID & Chr(34) & ")"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi

I am looking to append a new record to a joining table (TblBookings)

TblClients (Primary = ClientID)
TblTrip (Primary = TripID)

tblBookings
BookingID
ClientID
TripID


This does not work :-(

Private Sub ButUpdateBooking_Click()
'save record'
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'this is section that causes the problem'
DoCmd.RunSQL "INSERT INTO [tblbookings] Set [TripID] =" & Me.TxTTripID
DoCmd.RunSQL "INSERT INTO [tblbookings] Set [ClientID] =" & Me.txtClientID

'other form requery'
Forms![frmAddNewMember]![frmAddNewMember_List].Form![MembersList].Requery

End Sub


the 2 unbound boxes (starting txt) are filled with other code and work fine.

Obviously the INSERT INTO is not working - any ideas please

Thanks for any tips