×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

INSERTING unbound values into table

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

RE: INSERTING unbound values into table

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")

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

(OP)
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")
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

(OP)
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

RE: INSERTING unbound values into table

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close