Filtering records in a Form

[JHNielson]

I have three related questions for an update form I'm building and hope someone can help:

I have a table called [Master] and I want people to be able to review those records and if they need to update them. I have made a form for viewing the records.

1) The first question is how do I make a drop-down menu of the [Rec_ID] (the primary key for the table) for them to choose what to enter. Is there a way to put other filters in drop downs (i.e. by [Owner], [Date] etc.)


2) The second question is I want ot put a button so that when they are done entering the updates in the form, the record they are working on gets saved to the [Input_TBL] to be processed again with the new updates. I had one idea of how to do this:

• Create an append query from [Master] to [Input_TBL]

• Use criteria on [Rec_ID] of dlookup([Rec-ID], [Forms]![Master_FRM])

• Put a button on the form that runs this query.

I don't know if this will work or if there is a potential problem with this setup


3) The last question, is I want them to be able to see the full table so they can lookup the [Rec_ID] they are going to need to change. But I don't wnat them to be able to actually change the records. Is there a way to do this?


Thanks in advance for any help.

[JHNielson]

I hate to have to bump this, but I am really having some trouble trying to resolve this from issue. Again thanks to anyone who can help.

[JHNielson]

I tried to setup the query using an append query. and I set the criteria for the [REC_ID] to:


DLookUp([REC_ID],[Forms]![Review Master Records])

and I get a "Data type mismatch in criteria expression" error. Even though both records are set to Text.


When I set this to (which is what I thought the expression should be):
DLookUp("[REC_ID]","[Forms]![Review Master Records]")

I get an "Unknown" error.


I don't know why this query isn't working.


Any help would be greatly appreciated.

[JHNielson]

To resolve the filtering on the form by teh Drop-Down box, I tried assigning the Record Source to:

SELECT * FROM MAST_REC_LOG WHERE REC_ID=Forms![Copy of Review Master Records]!REC_ID; 

But the form doesn't update.


Thanks again for any help anyone can give

[rockdc1981]

dont know if this could help you,,,
on form design, insert a combo box on your header, a wizard will appear giving you 3 choices (lookup wizard i guess), choose 3rd choice which gives you the table record source, select the primary key and the value you wanted the user to select..
the selected record on the combo box will display all the record related to the field..hope i was able to help you

[MMcCarthy]

I have three related questions for an update form I'm building and hope someone can help:

I have a table called [Master] and I want people to be able to review those records and if they need to update them. I have made a form for viewing the records.

1) The first question is how do I make a drop-down menu of the [Rec_ID] (the primary key for the table) for them to choose what to enter. Is there a way to put other filters in drop downs (i.e. by [Owner], [Date] etc.)

The Row Source of a dropdown list can be based on any query you choose.

2) The second question is I want ot put a button so that when they are done entering the updates in the form, the record they are working on gets saved to the [Input_TBL] to be processed again with the new updates. I had one idea of how to do this:

• Create an append query from [Master] to [Input_TBL]

• Use criteria on [Rec_ID] of dlookup([Rec-ID], [Forms]![Master_FRM])

• Put a button on the form that runs this query.

I don't know if this will work or if there is a potential problem with this setup

You don't need a DLookup just set the criteria as [Forms]![Master_FRM]![Rec-ID]

3) The last question, is I want them to be able to see the full table so they can lookup the [Rec_ID] they are going to need to change. But I don't wnat them to be able to actually change the records. Is there a way to do this?


Thanks in advance for any help.

Create a form of all the records. Set the Allow Additions, Allow Deletions and Allow Edits properties to no. Make this form a subform of the main form.

Mary

[JHNielson]

Thanks for all the help. The one problem I am having is that I have done both:

Setting the record source to the select query i mentioned above,
I have also set the record source to a query on the [Master Table], with the critreria set to [Forms]![Master Records]![REC_ID]


and neither of them update the records.


I'm not sure how to fix this

Also I want the users to be able to update the records that is pulls up, so I need to know the best way to create/design this form.

[MMcCarthy]

Thanks for all the help. The one problem I am having is that I have done both:

Setting the record source to the select query i mentioned above,
I have also set the record source to a query on the [Master Table], with the critreria set to [Forms]![Master Records]![REC_ID]


and neither of them update the records.


I'm not sure how to fix this

Also I want the users to be able to update the records that is pulls up, so I need to know the best way to create/design this form.

Add the line Me.Requery to your code.

[JHNielson]

Great! I got it to work.

Thanks everyone!

[MMcCarthy]

Great! I got it to work.

Thanks everyone!

You're welcome.

[NeoPa]

I hate to have to bump this, but I am really having some trouble trying to resolve this from issue. Again thanks to anyone who can help.

JH,
You're absolutely fine bumping after 24 hours (Yours was even longer so no probs at all). We don't have many threads that don't get any answer at all for that long.
You seem to be all sorted now, which is good, but there is a tutorial thread (Example Filtering on a Form.) which you may find interesting anyway.