I have a list box with 3 columns and one line on my form and am writing an update query that is to use the value from the first column of the query to update a record in a table. I have referenced the list box as ListBox.Column(0) but the code displays a value of null when I run it and the record that is to be updated is updated to Null (it is blank). How do I write the code so that the value that is in column 0 of this list box is passed through the code?
Basically, in order for a list box to report values, one has to have been selected (either automatically or by the user).
What I'm getting here is that you have a list box, and you want a query to use the values from the selected item in the list box, correct? Generally, when that's the case and your query is pulling Null, it's because no value was selected (meaning no line is highlighted in the list).
You generally avoid that by putting a check for null in the code, with the procedure aborting and the user getting a message of some kind if a null is found.
My list box has a query as the control source which checks a table and shows 3 columns, of only one record. The results are produced by a choice made in a combo box that is on the form. I somehow need to use code to instruct access to write the information that is displayed in the first column of this list box to the table into a specific field in a specific record. Maybe a list box is not the correct choice to use here but I have been going around in circles over this problem for 2 days now. Using this list box is my latest attempt to update this record.
There is no user input to this list box and it only has the one record. Is there some way to force the selection of this record to produce the results I need. (reporting a null value gets me no where - exactly where I am now) OR is there another control that would work to access these records?
How about a different approach? You said you're already selecting a single record via the combo box. Why not use the combo box to provide the where clause to a DLookup, and then insert the result from that into your query?
Thank you - that is not one of the methods that I have tried. I am not sure how to use the results of the DLookUp in the update query - I can write the DLookUp code and the Update statement, but what would I use in the set portion of the Update statement to reference the results of the DLookUp?
Thanks for helping!
I assume you're doing this in VBA? Easiest way is to create a string variable, and then you have two options. If you set up the update query to use a parameter for the value being updated (like when you put [ValueHere] in the Update To box in the query builder), you can use a querydef object, update the parameter to = the string variable, and then execute the query. If you build the query directly in VBA, you'll need to concatenate it in.
Yeah mud. I will have to research some of the terms in your post to see if I can figure it out. I will probably post back sometime tomorrow with the code as I have it and maybe you can help me fill in the missing parts.
OK so I tried to create the statement using DLookup and here is what I have.
DoCmd.SetWarnings False
Dim varX As Variant
varX = DLookup("[CASE GTIN]", "[UPC TBL]", "[UPC Code]" = Forms![PP FRM]![UPC Code])
Dim strSQL As String
strSQL = "UPDATE [PP TBL] SET [PP TBL].[CASE GTIN] = " & varX & _
"WHERE ((([PP TBL].[PP ID])=" & [Forms]![PP FRM]![ID] & "));"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
I have having 2 problems - first the probably easier on:
The Update statement is giving a 3144 error and for the life of me I can't see it. Anyone not so close to this as I am please tell me what I've done wrong.
AND the biggest problem is that the DLookUp is returning a value of Null. There is a record in the UPC TBL in the UPC Code column that matches the UPC Code on the form and there is a CASE GTIN number in that record yet it return a value of Null. What the heck is going on? These numbers are stored as text.
I am really hoping someone can see the errors of my ways here (I have never written code using a variant, so I am not even sure the code is correct, but it is only balking as I mentioned) I have been kicking this problem around for 3 days and have approached it in about 5 different ways. Please help if you can.
Notice the single quotes. They're vital when concatenating text. That's something you definitely will need to get down when using code and variables. THIS article may help.
At least with numbers, you don't need to worry about which type of quote to use!
As to the update statement itself, the issue would again be concatenation. A good thing to do when you're getting this error is to put a break immediately after the variable is generated, and debug.print the variable. That way, the precise string you created is listed in the immediate window in your editor to look at. In this case, I can see that you're missing quote marks inside the string. (Think of it as levels or loops, and as you go farther 'inside', you need to alternate between ' and " to contain them.)
it's the difference between
Code:
strName = Bob The Dinosaur
and
Code:
strName = "Bob the Dinosaur"
If you still don't see what to do to fix it, let me know and I'll rewrite it after I get home from dinner.
Edit: Oh, and as a side note, it is generally considered a bad idea to have spaces inside object and variable names. It makes referring to them a bit more complicated than it has to be (for example, the brackets in domain functions are unnecessary when there are no spaces), and makes it a bit harder to tell what you're looking at. That's why you always see folks here doing things like tblEmployeeData, strFirstName, and the like.
I tried your suggestion (sorry for the delay in reading your message - I don't get email notifications even though I have it selected) but I get a syntax error. That goes away if i remove the ' and the '" (as shown below) but the debug shows "null". The upc IS text.
