Opening a form using SQL and Recordsource

[ekayp]

Hi, I've tried to open a form using a button from a menu form. The button onclick property creates a SQL join (1-1) between 2 of my tables and then sets the recordsource property. The form opens fine but the record selector always shows all records whilst the form is on the 1st record but when I move to record 2 it applies the where part of my SQL statement. I've got the following sequence of events in my vba code and I wondered if anyone can suggest a solution as to why it does not work:
strSQL = SQL statement;
DoCmd.OpenForm "X", acNormal
Forms!X.RecordSource = strSQL
Forms!X.Refresh
Forms!X.fieldY.SetFocus
Bizzarrly, it works ok on other forms. I'm using access 2010 and 2007 . Anyone come across this problem? Is there another method instead of Refresh I should be using?

thanks,
ek

 

[RainLover]

Try using a Bound Form instead.

Also why do you have a 1 to 1 Relationship.

 

[ekayp]

Hi,
In my main table/form I have all the IDs and few common items, which are only input once. I've used this form to link to 10 other tables/forms and most of these are 1-1 links with the IDs. Saves having users to input the clientids in each time they go into the 10 forms. Not really 100% sure how to do look ups in access and had wondered if I should have gone down that route.
The problem with creating a bound form is that I've got to filter the data entry by 3 categories and I had used those in the where statement of the SQL statement. This allowed me to have 10 forms instead of 30 odd.
Thanks,
ek

 

[RainLover]

ek

You appear to be on the right track but you still need to use Bound Forms. There is no advantage in using Unbound forms.

You need to Normalise better.

Suggest you post a copy of your relationships. Create a Pic and post that.

Will try to help if I can see your Relationships.

 

[ekayp]

Rain,
Thanks for your time. The problem seems to be fixed now: I moved the field from the IDs table to the header of the form instead of the detail part. Unsure why this was causing a problem with the code I had though.
regards,
ek

 

[RainLover]

At times I do stress a point. Suggest you use Bound Forms like the way Access was designed to do.

Life is much easier.

Bye for now.