INSERTING unbound values into table
INSERTING unbound values into table
(OP)
I've created 2 unbound combo-boxes on a form (one is synchronised to the other)
The user will type a business name + other details (which will go directly into the table 'h_main') I want the values for the 2 unbound boxes to also go into the table for the current record (the bit that confuses me a little). here is my code below.
'isn' is the link field I use
Obviously, the stLinkCriteria line isn't doing anything, as I'm unsure
Dim dbs As Database
Dim strsql As String
Dim stLinkCriteria As String
DoCmd.Requery "combo2"
Set dbs = CurrentDb
stLinkCriteria = "[isn]=" & Me![isn]
strsql = " INSERT INTO [h_main] (fd_bus_type) VALUES "
strsql = strsql & "(' & Type & ');"
dbs.Execute strsql
Help much appreciated
Tony
The user will type a business name + other details (which will go directly into the table 'h_main') I want the values for the 2 unbound boxes to also go into the table for the current record (the bit that confuses me a little). here is my code below.
'isn' is the link field I use
Obviously, the stLinkCriteria line isn't doing anything, as I'm unsure
Dim dbs As Database
Dim strsql As String
Dim stLinkCriteria As String
DoCmd.Requery "combo2"
Set dbs = CurrentDb
stLinkCriteria = "[isn]=" & Me![isn]
strsql = " INSERT INTO [h_main] (fd_bus_type) VALUES "
strsql = strsql & "(' & Type & ');"
dbs.Execute strsql
Help much appreciated
Tony
RE: INSERTING unbound values into table
For an INSERT you want the following syntax...
INSERT INTO TABLE1 (FIELD1,FIELD2) VALUES ("X","y")
If it's an UPDATE you want...
UPDATE TABLE1 SET FIELD2 = "Z" WHERE FIELD1 = "X";
Obviously, you replace my definitions with your own.
Regards,
Shep
RE: INSERTING unbound values into table
So its an update I'd need
Presuming my tablename = h_main
The 2 table fields to be updated = 'fd_bus_type' & 'fd_bus_cat'
The 2 unbound field names = 'Type' & 'Combo2'
I've tried the following code without success, is it wildly wrong?
[Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("h_main")
With rst
.Update (dbUpdateCurrentRecord)
!fd_bus_type = Forms!food_general![Type]
!fd_bus_cat = Forms!food_general![Combo2]
End With]
Tony
RE: INSERTING unbound values into table
Dim dbs As Database
Dim rst As Recordset
Dim strOld As String
Dim strNew As String
Dim strMsg As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("h_main", dbOpenTable)
With rst
.Edit
strOld = !fd_bus_type
strNew = !fd_bus_cat
!fd_bus_type = Forms!food_general![Type].column(1)
!fd_bus_cat = Forms!food_general![Combo2].column(1)
strMsg = "Edit in progress"
If MsgBox(strMsg, vbYesNo) = vbYes Then
.Update
Else
.CancelUpdate
End If
MsgBox "Data in recordset = " & !fd_bus_type & " " & _
!fd_bus_cat
End With
RE: INSERTING unbound values into table
The idea is:
Set rs = me.recordset
With rs
!field1 = cboName
!field2 = cboOther
End With