Hi,
I thought about posting this in the SQL forum, but it's not quite as complex as the questions i see there.

I'm trying to use a text box and list box with the text box serving two purposes.

The list box is populated with a list of companies to select from. The text box can be used to narrow down the list box and the rest of the information is stored with the selected company.

I also want to be able to add a new company to the list using the same text box and a command button. So far, I've been able to find out that I need to use an INSERT INTO statement, but I can't seem to get the syntax right for adding what is in the text box to the table tblCompany. I know it's supposed to be something like this:


And then a requery to show it on the list.

Obviously, this is incorrect. But I couldn't find anything on the web that showed how to reference a control on a form in this particular SQL statement. Getting just this part to work would be great since I'd like to use it for another control as well. But I would also like to use VBA to get the lsit box to select it once it's been entered. For that I really have no idea how it's done.

Any help is appreciated, thanks!

Not sure what 'default' is supposed to be in the list of values. If companyID is an AutoNumber field, you simply leave it out of the statement:



Note that will fail if there's an apostrophe in the company name. To be able to handle apostrophes and double quotes both, use



Note the use of the Execute method rather than RunSQL. For an explanation of why, see what Allen Browne has at Action queries: suppressing dialogs, while knowing results

Note, too, that the keyword is VALUES, not VALUE.

Your syntax is a little off and here is a little better command to execute through.

~only applies if using the word default. If default is a value, then set it up like the second value
isql = "INSERT INTO tblcompanies (companyID, company) VALUE ('default', '" & forms!frmCallsMain!txtcosearch & "')"
CurrentDB.Execute isql

Thanks guys!

I was using default for the autonumber field. I ended up using Doug's solution and works just the way i want.

Thanks again!

Again, I thank you for your help, but I have just one more issue.

I need to use the same code for another box on my form. This would be for the contact. I have my tables set up like this: tblCompany<=tblContact<=tblCalls

So the contact would be entered for the company selected in the company list box. How can i get the code to enter the contact name using the companyID of the selected company, and also enter information from two text boxes, one for name and one for e-mail. The fields are companyID for the foreign key, name for the contact name and email for the contact e-mail, all are in tblContacts.

Again, thanks for your help!

One approach is to use DLookup to retrieve the ID just assigned to the name.

Another is to use a recordset to add the value, and then see what ID was assigned to the new row.

Doug,
I'm familiar with the dlookup function, but not sure how I would include it in the syntax of the INSERT INTO statement. And also I'd like to know how to reference a second text box.

Thanks for your help!

You don't put the DLookup into the INSERT INTO statement. After you've run the first INSERT INTO statement, in your VBA you determine what ID was associated with the company you just added. You can either store the value in a variable, or store it in another text box on the form (the text box needn't be visible)

You can use @@IDENTITY to get the last id generated.

Okay, so I have my invisible text box returning the companyID, but where do I go from there? I'm guessing that I'd be using something like INSERT INTO...SELECT...FROM...WHERE...VALUES??

Not even sure if that's the right order. And no clue on the syntax. And also still need to know how to change it to add to two fields at once. I'm learning this SQL little by little, but it's tough without any actual education or even a book on the subject. So all I can do is guess a basic outline:

INSERT INTO tblcontacts (name, email) SELECT companyid FROM tblcompany WHERE companyid=txtcompanyid VALUES (txtname, txtemail)

Just a guess, am I even close?

Thanks again for any help!

What are you trying to do?

You can't combine SELECT and VALUES in the same INSERT INTO statement.

Guessing at your table setup, you probably want



(the reason you don't need to use the Replace function on txtemail is because apostrophes aren't valid in e-mail addresses)

It seems like you are struggling with grasping the basics of an sql statement. Here is a REALLY good website that has multiple examples and usage for each statement. I would strongly suggest that you take a look through a couple of them so you can get a firmer grasp on how sql statements work.

Doug,
There's something about the code you gave that it doesn't like. When I try to run it, it says "syntax error (missing operator)...".

I entered it like this:



The extra code is for data integrity, and so that the text boxes clear after the button is pressed. So I don't know if I did something wrong or if it's missing a symbol or what.

And bulsatar, I struggle with a lot of things haha and SQL and VBA are very new to me, so that's a definite. But I have actually seen that site before, just didn't get too in-depth with it to the point of finding out what all the " ' & and so on are for. But I thank you for the advice and I'll definitely have to look into it more.

Any help on this is appreciated, thanks!

Sorry, typo. There's a single quote missing.

ah ha...all of the '" & are just vba's way of connecting everything together. So, in words, lets say you want the value of a combobox (which is a string) to be at the where condition of some select statement. So verbally you would say, "I want to Select all of my field1 and field2 entries From my table Where field1 equals my combobox value". In vba, you would type out the first part of the string and then combine that with the combobox value, but you have to be able to let vba know what is part of the string is sql and what is the value of the combo box. So, we will have to stop the string and then combine (or concatenate) the combobox on to the end of the string.

Start the string: "SELECT field1, field2 FROM myTable WHERE field1 = ' "
you will notice that I used an extra space to identify where the single quote is and the double quote is (for clarity). When using sql, you will have to differentiate between strings, dates and numbers. Strings need to be surrounded by single quotes ('Me string'), dates by pound signs (#Me/Date#) and numbers do not need anything (123). Now, add the combobox value to the end of the statement

"SELECT field1, field2 FROM myTable WHERE field1 = '" & combobox1.Value
but we aren't done because since you are passing a string to the jet engine (the part of access that handles queries) you still need a closing single quote and then you need to finish off the string

"SELECT field1, field2 FROM myTable WHERE field1 = ' " & combobox1.Value & " ' "
SPACES added for clarity, normally you would have the single quotes right next to the double quotes.

It is easier to see what you sql string looks like if you set your string to a variable and then on the next line put something like debug.Print YourVariable. Then you can open up the View->Immediate Window to see what the string looks like.

Hope that helps