Forms help for newbie

[oaishm]

I've look in so many books. I want to make this super easy form. I have an unbound text [code1] in form [frmTest] which is itself bound to a query select * from test where code1=forms!frmTest!code1
I made a bound text field in the form called date which is bound to the query field date. I want the date to change when I change the code field.


I made an event for after update of field [code1] which was just simply forms![frmTest].requery

This has failed on many fronts. I miss php and mysql. Anyway

When do we refer to fields as forms!frmTest!code1 or frmTest!code1
How do I actually link a query to the value of a field in the form
How do I reset the form after there is a value in the form so the underlying query is reset
how does the form get updated with the new data

 

[SydneyGeek]

To requery the same form, put this in the AfterUpdate event of the text box:
Me.Requery

You can use Me for forms and reports.
The Forms!SomeForm!SomeControl syntax is used in criteria for forms, combos and list boxes. In those circumstances you need the full path to the control whose value you are using.

Denis

 

[oaishm]

I tried it. I can't tell if it works or not. When I go to the form view, there are no controls, likely because there is no data. All I want is an unbound field. I type somehting into the field. A query will be run based on that parameter. The query updates the form my unbound field is a part of. I avoid having one form where we get a parameter which then updates a query and opens another form and then closes. That seems a lot messier. Please help!

 

[SydneyGeek]

See if this does the trick for you

Denis

 

[oaishm]

I can't seem to make it work. I'm not sure why. I set the record source of my form to select * from French_Rev. I selected the combo box row source to select distinct code1 from French_Rev. Then I used the following procedure:

''==========================================================
''Making a selection from this control filters the Orders form
''for orders by the selected customer.
''Created 19 Sep 2007 by Denis Wright
''==========================================================
'declare variables
Dim sFilter As String
'in this case, the ID is text so the ID value
'needs to be wrapped in single quotes.
sFilter = "[Code1]= '" & Me.Combo2 & "'"
'assign the filter value,and turn filtering on
Me.Filter = sFilter
Me.FilterOn = True

I'm not sure what the right syntax is for the record source of the form. I figured if I made the record source = Select * from French_Rev where Code1 = Forms!Form1!Combo2 that would filter it and I'd be able to make text fields equivalent to the fields in that query. Alas.

Then I tried this filter item. Text fields reflect the query fine, but I might as well made the source of the form a table instead of a query. The filter portion of it did n't work at all.

 

[SydneyGeek]

Just checking:

Code1 is a text field, not a number
The combo name is correct, and it only has 1 column (from Code1) in the Rowsource
The code that you posted is in the Combo2_AfterUpdate procedure

Denis

 

[oaishm]

Oh no! You may be on to something! Code1 is an integer. Do I have to convert it to a string? I haven't needed to do something like that since Highschool, what with php, javascript etc so loosely typed. Well, I'm going to try and see if that's the issue. There must be a function that turns numbers into strings like cast

I'll reply with the answer. This has been weeks of head beating

 

[SydneyGeek]

Change this...

sFilter = "[Code1]= '" & Me.Combo2 & "'"

to this

sFilter = "[Code1]= " & Me.Combo2

Denis

 

[oaishm]

Well, that didn't work. It seems like such a simple thing. Tie a form to a query with one parameter or more. use a text field in the form to update that same query. Update that same form after the query is updated. This seems like a lot less data than pulling down the entire table and filtering it. Well, anyway, I checked all three things. My combo box has the requisite data that it is bound to. The form contains the first record of the query. I'm going to try to open a message box of some sort just to see if the procedure is firing when I update the combo box.

 

[oaishm]

Well, that didn't work. It seems like such a simple thing. Tie a form to a query with one parameter or more. use a text field in the form to update that same query. Update that same form after the query is updated. This seems like a lot less data than pulling down the entire table and filtering it. Well, anyway, I checked all three things. My combo box has the requisite data that it is bound to. The form contains the first record of the query. I'm going to try to open a message box of some sort just to see if the procedure is firing when I update the combo box.