INSERT using a variable

[FooFighter]

I'm having a bear of a time here. I have a table that I want the user to be able to insert new data into (this new data will only be 1 letter). Anyway I've made a small query that accepts an @variable. When I run the query I get an input box asking me for the variable and inserts my data into the table like it should.

Where my problem arises is I need to be able to let the user click a button, enter the desired data into an input box, then update the listbox that contains the data including the new one the user just entered. I originally wanted to update 2 fields in that table by having another inputbox pop up after the 1st was entered. Figured that'd be next to impossible though.

For the life of me I can't figure out how to send the input box data to the listbox via VBA. Can anyone guide me on this please?

This is my query code...

INSERT INTO tblIndicator ( [Indicator] )
SELECT [@Indicator] AS Expr1;

[mshmyob]

Try doing a requery of the ListBox after data has been entered in the table.

[code=vb]
Me.lstBoxName.R equery
[/code]

cheers,

[FooFighter]

The ReQuery command will be the last line of my code. I can't figure out all the lines that would come before that. How do I accept a value from the form via an input box, then pass that variable to a query, then lastly I will update the listbox via the ReQuery command.

In a nutshell how would I get the data from the form to the table?

[NeoPa]

You talk about various levels without being clear as to what exactly you're after. Are you wanting to use the InputBox() function to provide data that you subsequently wish to insert into a table? Is the ListBox control simply a reflection of this table? If so, then it's a bit of a red-herring and only confuses the question. If not, then your question is even less clear and you will need to clarify what it is you are asking.

Assuming I have your requirement sussed, you would create a SQL string within your VBA code, which includes the value just grabbed from the InputBox() function. The format of the SQL string would be, assuming the value entered were a string BLOB :

INSERT INTO tblIndicator ([Indicator])
VALUES ('BLOB')

I assume you have the understanding of how to create such a string (We can help otherwise of course).
In case the value is not a string, see Quotes (') and Double-Quotes (") - Where and When to use them.

[NeoPa]

For the life of me I can't figure out how to send the input box data to the listbox via VBA. Can anyone guide me on this please?

I should have read this again after I'd worked out what I thought you were asking. I was so struggling to understand what you were saying I overlooked this clear bit of the question.

Here is some example code for how you would get a value into the said string using VBA :

Dim strSQL As String, strValue As String

strSQL = "INSERT INTO tblIndicator ([Indicator]) " & _
         "VALUES ('%V')"
strValue = InputBox("Please enter Indicator value :")
strSQL = Replace(strSQL, "%V", strValue)
Call DoCmd.RunSQL(strSQL)
Call Me.SomeListBox.Requery

PS. I should add that your form of the SQL is equally valid and will work perfectly well. It is possibly more standard to use the VALUES version when dealing solely with provided values, but either works fine.

[FooFighter]

Actually I figured out another way to do it with the following code. If anyone else is trying to do something like this here's a nice easy way.

Dim strSQL As String 'String for the INSERT SQL statement
    Dim strInputIndicator As String 'String to store the indicator letter
    Dim strInputDesc As String 'String to store the Description of the indicator
    
    DoCmd.SetWarnings False 'Don't display the warning about inserting a row
    
    strInputIndicator = InputBox("Enter a one letter code for the new indicator", "New Indicator Code") 'Ask the user for code
    strInputDesc = InputBox("Enter a short description for the new indicator", "New Indicator Description") 'Ask the user for description
    
    strSQL = "INSERT INTO tblIndicator ( [Indicator], [Description] ) VALUES (" & strInputIndicator & "," & strInputDesc & ");"
    
    DoCmd.RunSQL strSQL 'Run the INSERT query
    
    lstIndicators.Requery 'Update the list box to show new indicator
        
    DoCmd.SetWarnings True 'Reenable warnings

[NeoPa]

Have you tried running this code?

I would expect to see a run-time error on line #10. You give no indicator of the data type of your field Indicator, but from the prompt in line #8, it seems clear the field Description is a string. A string literal (not reference or variable) must be enclosed in quotes (See link in post #4).

[FooFighter]

Yeah I was getting an error, but I managed to fix it and get the thing working. Thanks for taking a peek over my code though :)

[NeoPa]

Good for you.

Can I take that to mean the whole problem is resolved now, or does that refer just to that line of code being ok now?

[FooFighter]

The entire question is solved :)