Form variables in queries?

[dalascby]

I am very new to Access so forgive me if this seems like a very elementary task. Here is what I need to do:
1)
I have five fields on a form, lets call them var1, var2,...var5. Var1 is an unbound text field, when I enter a code into var1 and hit enter (or tab, or click into another field) I want the value of var1 to be used in a query (if a query is best to use) to extract data from a table and fill in var2 through var5. (The value that I key into var1 will correspond to a field/column called "itemno" in the table...itemno is unique within the table.)

2)
After vars 2 through 5 are filled in after step 1 above I want the ability to change (key over) any one of those values(var2-5) and have it actually change the value of that column ,for that record, in the table (update the record in other words.)

Thanks in advance for any help that someone can give me on this.

 

[tlbroadbent]

I suggest a different approach.

1- Bind all five table columns to the form.
2- If users are not to update var1, then set the LOCKED property for that field on the form to TRUE. Y
3- Create another textbox called txtSelectVar1 or something like that.
4- Add the following code to the After Update event of txtSelectVar1.

Private Sub txtSelectVar1_AfterUpdate()
Me.RecordsetClone.FindFirst "[colname] = " & Me.txtSelectVar1
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

The record should be displayed on the form, the user can change it and Access will handle the table update automatically. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[dalascby]

Questions:
1) Do I replace colname with the actual column name from the table that corresponds to var1 ..i.e "itemno" ?

2) If I bind the other fields to the table that means that when the form is opened those fields will be initially filled in with values from the first record of the table. I need all fields to initially be blank upon entering the form. Only when I key an item number into var1 (and then hit enter) should values appear for the first time.

 

[tlbroadbent]

Yes, replace colname with the name of the column in your table.

If you don't want the form to be filled in automatically then do the following.

1- Create a form with unbound controls.
2- Add the text box as noted previously.
3- Add the following code in the After Update event of the text box.

Private Sub txtSelectVar1_AfterUpdate()
Me.RecordSource = "Select * From yourtable"
Me.Filter = "itemno=" & Me.txtSelectVar1
Me.FilterOn = True
Me.txtvar1.ControlSource = "itemno"
Me.txtvar2.ControlSource = "var2"
Me.txtvar3.ControlSource = "var3"
Me.txtvar4.ControlSource = "var4"
Me.txtvar5.ControlSource = "var5"
Me.Requery
End Sub

The user will be able to change the record. As soon as another record is chosen or the form is closed the record will be updated.

For additional ideas see thread181-129566. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[dalascby]

Terry, I'm getting a macro error:

"Microsoft Access can't find the macro '.' ....


I don't where this is coming from, I haven't added a macro, just your code (my version of your code). Why would it be looking for a macro with a period for the name??

I'm testing all of this using the Northwind database. I have a small form that is bound to Northwind's Employees table. FirstName and LastName are columns in the table.
I have 3 text boxes on the form, txtSelectVar1, txtFirstName and txtLastName . I want to be able to type a first name (ex. Robert) into txtSelectVar1; hit enter; and have txtFirstName and txtLastName fill in with Robert and King respectively (which is the 7th record in the table.)

Here is my code:

Private Sub txtSelectVar1_AfterUpdate()
Me.RecordSource = "Select * From Employees"
Me.Filter = "FirstName=" & Me.txtSelectVar1
Me.FilterOn = True
Me.txtFirstName.ControlSource = "FirstName"
Me.txtLastName.ControlSource = "LastName"
Me.Requery
End Sub

(Sorry for being such a pest.)

 

[tlbroadbent]

Which vesion of Access are you running? I don't think it should make a difference but it would be good to know.

Check all modules (sub routines and functions) in your database. You may have a spare '.' in one of the modules. Especially check the On Current, Lost Focus, Get Focus, On Enter events. Either delete any extra '.' or if not needed, delete the module.

Check this link for more info.

http://support.microsoft.com/support/kb/articles/Q209/5/82.ASP

Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[dalascby]

Thanks Terry, I removed all stray spaces and I no longer received the macro error.

Here is whats happening now, when I type a value into
txtSelectVar1 and hit enter it brings up an "Enter parameter value" dialog box and forces me to re-enter the value in this box. Why is this box popping up?

Thanks again for your expertise.

 

[tlbroadbent]

You've mispelled a name in the query or you have a parameter in the query. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[dalascby]

Once again, here is the code, I assure you there are no misspellings. Do you see anything else that would be causing the box to pop up?

By the way, I'm using Access 2000 (9.0.2720)

Private Sub txtSelectVar1_AfterUpdate()
Me.RecordSource = "Select * From Employees"
Me.Filter = "FirstName=" & Me.txtSelectVar1
Me.FilterOn = True
Me.txtFirstName.ControlSource = "FirstName"
Me.txtLastName.ControlSource = "LastName"
Me.Requery
End Sub

One more thing, when this is used in the actual application it will be on a subform inside of another form. Will that cause any wrinkles that we haven't discussed?