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

 

[oaishm]

No alas, it still doesn't work. I've now tried setting the rowsource of my form to select * from dbo_MenuItem and my filter to plu='Me.Combo' which obviously is wrong. I've tried Combo0 and 736870059441. the 736870059441 works, but it isn't very dynamic.

Then I tried to put an after update event on my Combo box:

Private Sub Combo0_AfterUpdate()
Me.Filter = "PLU = " & Me.Combo0
Me.FilterOn = True
End Sub

and I get a you tried a you canceled the previous operation error, whatever that means. Google says it means my database is corrupted but I started a brand new database and just made two little tables to play with this. Unbelievable, this is never going to work.

 

[oaishm]

Finally. It took days!!

Here is the killer solution. If you're going to filter on a TEXT FIELD!!! you have to make this ridiculous syntax:

Private Sub Combo0_AfterUpdate()
Me.Filter = "PLU = """ & Combo0 & """"
Me.FilterOn = True
End Sub

That's right. Four quotes because Microsoft can't escape with a backslash like the rest of the world and the filter is not loosely typed like the rest of the world. I wonder now if I can just keep changing the form control source to "select * from dbo_MenuItems where PLU = """ & Combo0 & """"

Yup,

this works

Private Sub Combo0_AfterUpdate()
Me.RecordSource = "Select * from Menuitemsbeforemod092509 where PLU = """ & Combo0 & """"
Me.Refresh
End Sub


So does this

Private Sub Combo0_AfterUpdate()
Me.RecordSource = "Select * from Menuitemsbeforemod092509 where PLU = """ & Combo0 & """"
Me.Requery
End Sub

No reason for the difference.