OpenForm Macro

[hemolytucus]

Hi All,

I am Access noobie. I am creating a form that searches customers by suburb. My table structure is like this

tblCustomer tblPostCode
CustomerID
CustomerName PostCodeID
PostCodeID(FK) SuburbName

My question is how do i create a form that asks the user to input the suburbname and searches the customer belonging to that suburb based on their postal code? I have made a query that is attached to the textbox and a command button that opens the customer's table but it is displaying all either all records or none at all. I'm thinking its something to do with the where condition
Any help will be greatly appreciated

 

[pr2-eugin]

You might need to JOIN they two tables based on the PosrCodeID, then use the SuburName in the WHERE part, something like.

SELECT tblCustomer.CustomerID, tblCustomer.CustomerName, tblPostCode.SuburbName
FROM tblCustomer INNER JOIN tblPostCode ON tblCustomer.PostCodeID = tblPostCode.PostCodeID
WHERE tblPostCode.SuburbName = "Avon";

 

[hemolytucus]

Hi PR2-Eugin

Thanks for your reply. My query now works but I still can't make the open form macro open customers living in the suburb. I am bit confused what should I write on the WHERE clause of the OPEN FORM macro.

Thanks

 

[pr2-eugin]

Create a Form based on the Query selection. Then use the DoCmd.OpenForm method to open this Form. http://msdn.microsoft.com/en-us/library/office/ff820845.aspx

 

[hemolytucus]

Thanks PR2-Eugin!!!. You are awesome!