Help with forms/code on forms

[LaurieW]

I have a form where the user selects the records they want to see before going to the data entry form. The code on the selection form is on the On Click Event of a frame and is as follows:

If frameSelectSite = 1 Then DoCmd.OpenForm "frmDataEntry", , , "Group = 'Central/Scat Management'" Else
If frameSelectSite = 2 Then DoCmd.OpenForm "frmDataEntry", , , "Group = 'Dunedin Management'" Else
If frameSelectSite = 3 Then DoCmd.OpenForm "frmDataEntry", , , "Group = 'Exchange Management'" Else
If frameSelectSite = 4 Then DoCmd.OpenForm "frmDataEntry", , , "Group = 'Hi-Rise Management'" Else
If frameSelectSite = 5 Then DoCmd.OpenForm "frmDataEntry", , , "ProjectName = 'McDonough Homes'" Else
If frameSelectSite = 6 Then DoCmd.OpenForm "frmDataEntry", , , "Group = 'Mt. Airy Management'" Else
If frameSelectSite = 7 Then DoCmd.OpenForm "frmDataEntry", , , "ProjectName = 'Roosevelt Homes'" Else
If frameSelectSite = 8 Then DoCmd.OpenForm "frmDataEntry", , , "Group = 'Valley Management'" Else
If frameSelectSite = 9 Then docmd.close

This code works fine. The user is brought into the data entry screen and only those selected records are available.

On the data entry screen, I have a find name box that the user can use to quickly move to a record by using a pick list. My problem is that I can't get the names in this box to list only the names chosen from the first selection form. Instead, all names are listed from the entire database.

The find name box is a combo box whose Row Source/Type property is a Table/Query. The Row Source property is:

SELECT qryDataEntry.ID, [LName] & ", " & [FName] AS Expr1 FROM qryDataEntry ORDER BY [LName] & ", " & [FName];

Does anyone have any ideas how I can change this to list only the names the user chose from the first selection screen? I've tried everything I can think of but can't make it work...thanks.

 

[dcx693]

Since you are using the where parameter of the OpenForm method to tell the form which records to display, you can actually reuse that information when the form is open. The form's Filter property retains that information and you can get at it by asking for it.

Since the combo box in question is on the same form where the records are being displayed, the rowsource for your combo box can be something like:

"SELECT qryDataEntry.ID, [LName] & ', ' & [FName] AS Expr1 FROM qryDataEntry WHERE " & Me.Filter & " ORDER BY [LName] & ', ' & [FName];"
You can't type that expression directly into the rowsource for the combo box. Instead, assign it using the Open event of the form or another appropriate event using something like:
Me.combobox.Rowsource=the expression above. (Notice that I've changed the double quotes within the statement to single quotes.)

I'd also suggest changing the If Then Else If End If construction to something more readable. Try using Select Case or the Switch function. On the other hand, if it aint broke...

 

[LaurieW]

Thanks for your suggestion ... I'll give it a whirl!

 

[LaurieW]

Just to let you know ... that doesn't work. I don't get any names in my combo box; it's totally blank. Have tried it in many events and alterations, but no go!

Thanks anyway...

 

[dcx693]

Ah. I see the problem. Sorry, I think I see the problem. The qryDataEntry query must have the Group and ProjectName fields in it. Does it?

 

[LaurieW]

Yes, those fields ARE in the query already.

Thanks...

 

[dcx693]

Not sure where the problem is now. Try this: create a new regular query, but don't add any tables to it. Change from design view to SQL view (go to the View menu, chose SQL View), and copy and paste the following text into it, but exclude the opening and closing double quotation marks:

"SELECT qryDataEntry.ID, [LName] & ', ' & [FName] AS Expr1 FROM qryDataEntry WHERE Group = 'Central/Scat Management' ORDER BY [LName] & ', ' & [FName];"

Run the query by clicking the red exclamation point.

If there are matching records, it should display them. I'm just trying to see if the query actually works, and/or if there is a problem with the combo box setup. Try some other variations of the query by changing the WHERE Group = 'Central/Scat Management' to WHERE Group = 'Dunedin Management' etc... just to make sure it all works.

 

[LaurieW]

SUCCESS!! I tried making a new query as you suggested with only the SQL statement and received an error that my Where clause had a problem. I checked my SQL book and the problem was with the field name "Group" which is a reserved word in SQL. I changed the field name and code and everything else to GroupName instead of Group and it works!

Thank you so much! This one has been bugging me for days!!

 

[dcx693]

Aha! Cool. I should've spotted that, but alas, I am no SQL guru. Glad to have helped.