By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,487 Members | 1,801 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,487 IT Pros & Developers. It's quick & easy.

INSERT using a variable

P: 13
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...
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblIndicator ( [Indicator] )
  2. SELECT [@Indicator] AS Expr1;
Apr 26 '09 #1
Share this Question
Share on Google+
9 Replies

Expert 100+
P: 904
Try doing a requery of the ListBox after data has been entered in the table.

Expand|Select|Wrap|Line Numbers
  1. Me.lstBoxName.Requery
Apr 26 '09 #2

P: 13
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?
Apr 26 '09 #3

Expert Mod 15k+
P: 31,709
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 :
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblIndicator ([Indicator])
  2. 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.
Apr 26 '09 #4

Expert Mod 15k+
P: 31,709
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 :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String, strValue As String
  3. strSQL = "INSERT INTO tblIndicator ([Indicator]) " & _
  4.          "VALUES ('%V')"
  5. strValue = InputBox("Please enter Indicator value :")
  6. strSQL = Replace(strSQL, "%V", strValue)
  7. Call DoCmd.RunSQL(strSQL)
  8. 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.
Apr 26 '09 #5

P: 13
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.

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String 'String for the INSERT SQL statement
  2.     Dim strInputIndicator As String 'String to store the indicator letter
  3.     Dim strInputDesc As String 'String to store the Description of the indicator
  5.     DoCmd.SetWarnings False 'Don't display the warning about inserting a row
  7.     strInputIndicator = InputBox("Enter a one letter code for the new indicator", "New Indicator Code") 'Ask the user for code
  8.     strInputDesc = InputBox("Enter a short description for the new indicator", "New Indicator Description") 'Ask the user for description
  10.     strSQL = "INSERT INTO tblIndicator ( [Indicator], [Description] ) VALUES (" & strInputIndicator & "," & strInputDesc & ");"
  12.     DoCmd.RunSQL strSQL 'Run the INSERT query
  14.     lstIndicators.Requery 'Update the list box to show new indicator
  16.     DoCmd.SetWarnings True 'Reenable warnings
Apr 26 '09 #6

Expert Mod 15k+
P: 31,709
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).
Apr 27 '09 #7

P: 13
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 :)
Apr 28 '09 #8

Expert Mod 15k+
P: 31,709
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?
Apr 28 '09 #9

P: 13
The entire question is solved :)
Apr 29 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.