INSERT INTO syntax

  • Thread starter Thread starter Miranda
  • Start date Start date
M

Miranda

hi, this is a simple question.

i have two combo boxes that users select from. I have a button, on click, i
want these values to be inserted into my table (which has only two fields).
could someone give me an example how to do this. Thanks!
 
hi, this is a simple question.

i have two combo boxes that users select from. I have a button, on click, i
want these values to be inserted into my table (which has only two fields).
could someone give me an example how to do this. Thanks!
Click to expand...
I'll assume the 2 combo boxes are on the same form, and that the bound
column of one is Text and the second is Number.

Dim strSQL as String
strSQL = "Insert into YourTable(FieldA,FieldB) Select " & chr(34) &
Me!Combo1 & chr(34) & "," & Me!Combo2 & ";"
CurrentDb.Execute strSQL, dbFailOnError

Combo1 is the Text datatype, Combo2 is the Number datatype

When run, the SQL would look like this:
"Insert Into YourTable(FieldA,FieldB) Select "Smith", 12345;"
Hope this answers your question.
 
hi,

yes, you assumed correctly about the text and number combo boxes. I've tried
your code, as below, however i get a compiler error on
CurrentDb.Execute strSQL, dbFailOnError

i dont have any ideas?!
 
Also, is there a way to do this with a macro? or with an expression? if so,
does anyone know the syntax???

stuck...
thanks!
 
hi,

yes, you assumed correctly about the text and number combo boxes. I've tried
your code, as below, however i get a compiler error on
CurrentDb.Execute strSQL, dbFailOnError

i dont have any ideas?!
Click to expand...

You're missing a Reference or you don't already have one set to the
Microsoft Access DAO library.

The machine may have a missing reference.
Open any module in Design view.
On the Tools menu, click References.
1) Check your references that one for the Access DAO library is
checked.
If you don't see it, use the dropdown to find the highest version and
check it.

Also, if one is actually marked MISSING, click to clear the check box
for the type library or object library marked as "Missing:."

An alternative to removing the reference is to restore the referenced
file to the path specified in the References dialog box. If the
referenced file is in a new location, clear the "Missing:" reference
and create a new reference to the file in its new folder.

See Microsoft KnowledgeBase articles:
283115 'ACC2002: References That You Must Set When You Work with
Microsoft Access'
Or for Access 97:
175484 'References to Set When Working With Microsoft Access' for
the correct ones needed,
and
160870 'VBA Functions Break in Database with Missing References' for
how to reset a missing one.

You can also not use CurrentDb.Execute.
Instead use:
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
 
hi, this is a simple question.

i have two combo boxes that users select from. I have a button, on click, i
want these values to be inserted into my table (which has only two fields).
could someone give me an example how to do this. Thanks!
Click to expand...

Miranda, this is a rather advanced technique - using an unbound form
and updating the table in code, using an append query.

Why are you doing it the hard way?

Is there some reason you can't simply use a bound form, with bound
combo boxes? No code and no query needed at all...

If you do have a need to do it this way (and you may!) it can be
done, but I'm just wondering if you're not going the long way around!
 
This is a snippet of code taken from my example (which was a waste o
time posting as nobody has bothered to take a look) the answer to you
question is there.

Code
-------------------

Dim db As DAO.Database, rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblLoans")
With rs
.AddNew
.Fields("CustomerID") = Me.cboCustomers.Column(0)
.Fields("BookID") = Me.cboAvailable.Column(0)
.Update
End With

rs.Close
db.Close
Me.Refresh
 
Back
Top