How do I use a form value in a query expression

[chuckcoleman]

I have an append query that needs to get the value from a text box off of a form. The Field in the query is an Expression. I am using: Expr1: [Forms]![FormName].[ControlName]

My "logic" is that I would pull the value from the ControlName from the form and then use that to append it to the field I have selected in the Append To: row in the query. Note, the form is not bound to any table or query; it gets data in each text box by using either default values, DLookUp's, etc. The form displays the data correctly. When I test the append query, the result for Expr1 is a character, not from the alphabet, kind of like the letter "G" with some squigles above it. What am I doing wrong?

 

[CBrighton]

Try this:

[Forms]![FormName][COLOR=red][B]![/B][/COLOR][ControlName]

 

[chuckcoleman]

Same result. Actually, in my original post I mistakenly used the ".", I did have a "!" in the expression.

 

[CBrighton]

I assume the form is open and the control has something populated in it before you run the query?

 

[chuckcoleman]

The form is open and the control has something popluate in it before I run the query. Yes.

 

[CBrighton]

What do you see if you do the following, the data you expect or the strange character?

MsgBox [Forms]![FormName]![ControlName]

 

[chuckcoleman]

I tried: MsgBox [Forms]![FormName]![ControlName] and got an error, "The expression you entered contains invalid syntax." I tried: Expr1: MsgBox [Forms]![FormName]![ControlName] and got the same.

 

[CBrighton]

Sorry, msgbox was VBA code rather than something to go into the query.

Create a control button and use the VBA editor to create the on click event. Then just paste the code I provided in (and replace the FormName & ControlName placeholders with the real form & control name).

When you click the command button it should display the value currently stored in the control.

 

[chuckcoleman]

On the form I created a command button and created with the On-Click event vba code: Me.ControlName When I clicked on the command button with the form open and the correct data values shown in the text boxes, I got a MsgBox that DID display the correct value.

 

[CBrighton]

I think we may be at the point where a sample database would be useful.

Can you import the form and query (and a table with a dummy record or 2) into a new .mdb and attach it?

 

[chuckcoleman]

Here you go. When you open the form, ignore the #Error in some of the text boxes. The text box that is the issue for this test is [InvoiceIDX] which is the second from the top. The append query is for right now just a select query until I can get the right value to show up in the Field row. Run the query and you will see what I mean when I get the wierd character.

 

[CBrighton]

Sorry, can you save it as an .mdb please?

I only have Access 2003 on this PC.

 

[chuckcoleman]

New file attached.

 

[CBrighton]

Change it to: CInt([Forms]![Payment Form for Contacts]![InvoiceIDX])

I assume that character is number 2463 in some character set, converting it to an integer will cause it to display correctly in the query.

 

[chuckcoleman]

That fixed it! Thank you very much. I'm not sure I understand why it was required. The underlying table where that number is being pulled is a Long Integer data type. Out of curriosity I used the CInt in the Forms text box and removed it from the query and it didn't work. I had to reinsert it in the query. Thank you again! I appreciate your focus and help.

 

[CBrighton]

Interestingly if you change the control from =2463 to ="2463" (text rather than numeric) it seemed to work without the CInt in the query.

No problem, and it goes to show how much easier it is to fix things by tinkering. A long thread discussing it and 8 mins after the .mdb is posted it's solved.

 

[chuckcoleman]

Well, it's got to be obvious that you're just awesome! Thank you again.