INSERTING unbound values into table

[Richey]

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

 

[Shep]

Do you want to INSERT a brand new record or UPDATE an existing entry in your table ?

For an INSERT you want the following syntax...
INSERT INTO TABLE1 (FIELD1,FIELD2) VALUES ("X","y&quot

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

 

[Richey]

Shep

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&quot
With rst
.Update (dbUpdateCurrentRecord)
!fd_bus_type = Forms!food_general![Type]
!fd_bus_cat = Forms!food_general![Combo2]
End With]


Tony

 

[Richey]

I've just been playing with it and have it working..................almost! it is updating the 2nd record??? not the current record, which is what I want. This is the code I have at the moment.

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

 

[Quehay]

If you're using the form for entry and bound controls from the form to update the same record it might make sense to update the other fields in the record by using a variable pointer to the form recordset and forcing an update before the form can move to a next/prev record (by cancelling update until a save button w/ a blnflag has been clicked). The !field1 and !field2 references will refer to the current record, correct?
The idea is:

Set rs = me.recordset

With rs
!field1 = cboName
!field2 = cboOther
End With