DoCmd.RunSQL "INSERT INTO (append)

  • Thread starter Thread starter Wayne-I-M
  • Start date Start date
W

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
 
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
 
Wayne-I-M said:
'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
Click to expand...

Are confusing INSERT and UPDATE statement syntax?

Single-record append query:

INSERT INTO target [(field1[, field2[, …]])] VALUES (value1[,
value2[, …])
 
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.



Jeff Boyce said:
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

Wayne-I-M said:
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
Click to expand...
Click to expand...
 
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

Wayne-I-M said:
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
Click to expand...
 
Back
Top