Help selecting a record from a filtered query 2

[KerryL]

What's the best way to "select" a record from a filtered query so that the record populates a form and is saved to a table?

Here's what I'm trying to do:
tblPers includes personnel records.
qryPers filters the personnel records by user input (last name or soc sec or both). Once the query produces the filtered list of records, I want the user to select the required record so that it populates frmPers and saves the filtered into to a second table called tblParticipant.

So if the user enteres Johnson when prompted by the query, all 14 records with last name of Johnson appear, then the user selects the one he's looking for. That record is then used to populate frmPers and is saved to tblParticipant.

If more explanation is needed, please let me know. Thanks for your help.

kerryl

 

[Lightning]

How is the user inputting the last name or SSN?

One way to achieve what you want is the use the query with its criteria as the recordsource of a listbox or combobox, which would therefore only be populated with the records that satisfy the criteria. Then simply select the required record from the list/combobox and use that to populate your second form.

HTH
Lightning

 

[KerryL]

The users will be prompted (out of the query) for a Last Name and then a SSN. The query creates a filtered list based on the user entering both or just one of those selections.

Once a record is selected from the filtered list, I want it to populate a form and save it to the corresponding table.

 

[Lightning]

Hi Kerry

I set up a sample Db using your posted info (tblPers, qryPers). From these I created 2 forms:

frmPers - displays the tblPers data

frmSelect - contains a listbox control with the control source set to qryPers.

When frmSelect is opened, the query asks for the user input to the 2 criteria (using an OR condition). This supplies only those records that satisfy either criteria to the listbox. (eg "Smith")

Using the DoubleClick event this form then calls the frmPers form using the selected row from the listbox as the criteria in a "DoCmd.OpenForm" call

DoCmd.OpenForm "frmPers", , , "[ssn] = " & Me.List0

Does this do what you need? i can send you the sample Db if you like.

HTH
Lightning

 

[KerryL]

I will give it a try and let you know if I get it working properly. Thank you.

 

[KerryL]

Lightning,
I'm having trouble with the step where the form opens with the data of the record selected. I can get a form to open with a click or double-click event, no problem. The struggle I have is getting it to open with the correct record in it.

Can you send me your sample DB so I can try and find what's missing on my form?

Send it here: kblehman AT ameritech DOT net
(I modified it for obvious reasons.)

Thank you!

 

[Lightning]

On its way.

 

[KerryL]

Thanks for the mini database with the example. I understand what you did there, but I'm running into a snag when I use my query as the record source for the list box like you did.

If my query contains filtering criteria, which it does in order to reduce a 185,000 record table down to a manageable amount, then when I try and create a cbo or listbox based on the query (qrySelPartic) I receive this informational error:

"No value given for one or more required parameters."


The problem comes from the fact that qrySelPartic contains [Enter Last Name:] criteria for the LName field. If I remove it, I do not receive the error above. But I need to perform that filtering step in order to minimize the selection list for the users.

Why can't I base my list box on a filtered (by user input) query?

Or perhaps the better question is, how do I set the query filtering parameters before opening the form that contains the listbox that requires them?

 

[Lightning]

Can you post your query here?

 

[Lightning]

5 Minutes later -

Don't worry about posting your query. I've modified the "frmSelect" form in the sample Db and sent you the updated copy with a working solution.

For others following this Thread, I added a series of command buttons to the form, captioned A, B, C, etc. In the on click event of each button, I put code to set the listbox's Rowsource property to select only those records from the Personnel table where the lastname begins with that particular letter, and then requery the form. This populates the listbox, from which a particular record can then be selected and displayed in the Personnel form.

HTH
Lightning

 

[KerryL]

Thanks Lightning,

I was able to fix the query problem. I thought about it last night and realized I was trying to call the query without putting the criteria parameters in there first. So I created an intermediate form where the users can enter a name or ssn and the query is performed using those fields as the search criteria.

The problem I'm now having is the double-click thing, where the user double-clicks on the record from the filtered list (in the list box) and it opens up another form and populates the fields in the new form with the record that was clicked on.

Here's the line I used, which I thought would work and is similar to the one you have in the DB you created for me:

DoCmd.OpenForm "frmParticipant", , , "[SSN]=" & Me.List2


I tried linking the record in the list box to a couple other fields in the query but no luck. Each time I double-click on a record, I'm prompted to enter the parameter, so I guess it's not grabbing the parameter like I think it should.

Just can't get it to open the form with the selected record.

TIA,
kerry

 

[Lightning]

Is your display form based on a query or directly on your table? If it is based directly on the table, the query shouldn't be an issue, and the form should open and display the selected record correctly, using the code that you have.

If the form is based on a query make it a select query with no parameters. Don't use the query you are using to populate the listbox.

HTH
Lightning

 

[KerryL]

The display form (frmSelPartic) is based on nothing, it has no record source. However, on that form is the list box that produces the records of participants; that list box is based on the query which is filtered by the name/ssn parameters input by the user (qrySelPartic).

Perhaps I'm not understanding a concept here. If I have a list box of records and want to take all the data from one of them to another form when I double-click on it, what needs to happen? What sort of link or connection needs to occur? Does the form I'm dragging it to need to be linked to the current form somehow? Or do I need to link a field from the list box to a field in the form I'm taking it to? What is it that I'm not grasping?

Thanks for your help--I appreciate it.

KL

 

[Lightning]

Sorry, I probably wasn't clear enough with my question. By Display form I meant the form that displays the final data - the details of the person you are looking for. In the case of the sample database, frmPers.

If this final form's recordsource is based on a parameter query, the form will look for the parameters required by the query when it opens, no matter what information you have already given the form in code.

By basing this final form directly on the table where the data is stored (ie tblPers) or on a Select Query with no parameters, the only parameters required are the ones in the OpenForm command. That is,

DoCmd.OpenForm "frmPers", , , "[ssn] = " & Me.List0

HTH
Lightning

 

[KerryL]

I'm sorry, but I'm just not having any luck with this. I get as far as your frmSelect just fine. But when I try and use a double-click command for a specific record, or even if I insert a cmd button to open up a form using data from the selected record, it won't work.

Sometimes the data form (frmpers in yours) opens blank, other times I'm prompted for the parameter. I even tried using the paramters required at the query, as you suggested, and it didn't work.

I'll keep hacking at it, but it's starting to kind of p*** me off a bit.

Thanks again for your help.

 

[KerryL]

Lightning,
I think I figured out why my DB doesn't work the same as the one you emailed me.

When you use frmSelect it's based on the qryPers, which requires the name or ssn parameters. So is mine. So far so good. Once a name or ssn is selected for frmSelect, the matching names are displayed in the list box. So are mine, so we're still on track.

But when you double-click a name from frmSelect it opens frmPers, which is based on a table with data already in it.
When I double-click on a name in my frmSelect, I need it to open a form with the record from the filtered qry (which is tied to a separate huge table) and then SAVE it to tblPers, not use tblPers to find a matching record.

IOW, your frmPers opens with whatever record was double-clicked in frmSelect because tblPers is the record source for frmPers.

But my requirements are a bit different. When I double-click a name in frmSelect, I need Access to take that record and put it into a form and then save it INTO tblPers, not find it in tblPers and populate a form with it.

The record source for my frmPers is tblPers, but only because that's where the new record (selected via the double-click in frmSelect) needs to be saved.

Does that make sense? If so, how do I pull my double-clicked record out of one table (via the filtered query), put it into a blank form and save it to a different table?

Thanks in advance,
kerry

 

[Lightning]

Hi Kerry

No, I haven't forgotten - just been busy.

But my requirements are a bit different. When I double-click a name in frmSelect, I need Access to take that record and put it into a form and then save it INTO tblPers, not find it in tblPers and populate a form with it.

OK, that's a bit different to what I had understood and have been working towards. Now I'm on the right track.

Firstly, I think you are confusing two processes here. The code that I sent you will extract the data from your original table and display it on a form. So far, so good. Then, the second process - saving the data to your second table - needs to be run. Since you are dealing with one record at a time, the easiest way to save the data is to run the process as in the example I sent you to get the data into the display form (frmPers) and then save the data into your new table via a command button on the form.

Make sure that frmPers Recordsource is still tblPers. Add a command button cmdSave to your form frmPers. In the button's On Click event add this code:

Private Sub cmdSave_Click()
[COLOR=green]' NOTE:  DAO 3.6 code[/color]
Dim Db As Database
Dim Rs As Recordset

    Set Db = CurrentDb
    Set Rs = Db.OpenRecordset("tblNewTable", dbOpenTable)
    With Rs
        .AddNew
        .Fields("SSN") = Me.SSN
        .Fields("LastName") = Me.LastName
        .Fields("Firstname") = Me.FirstName
        .Fields("Age") = Me.Age
        .Update
    End With
    Rs.Close
    Set Rs = Nothing
End Sub

Obviously, you can add as many fields here as you need.

Run the process to select a record and populate the form, and then click cmdSave to save the record. Close the forms and then open you table (tblNewTable). The data from the form should now be there in the table.

HTH
Lightning
(Updated sample db on the way)

 

[KerryL]

I'm still unable to get the selected record to populate a new form after double-clicking on it.

I linked the listbox to various fields (I tried SSN, name, and others) but no matter which one I link it to, I can't get the form to open with the fields populated with the data from the record I double-clicked on.

It's important that I get the record into the form and also to link it to a master record, because these records appear in a subform when their CustID field matches the one in the master form.

Sorry for the confusion.

 

[Lightning]

can you send me a copy of your db with some sample data?

bryan dot fitzpatrick AT dotars dot gov dot au

HTH
Lightning

 

[KerryL]

Bryan: (I have a son named Bryan)
The main customer table I link to is a mainframe table, which you won't have access to. So emailing the DB to you without that link won't work.

The only option I have to get around it would be to extract some of that table's data into a temporary table within the DB and fool the program that way. Pointing the forms to the temporary table and recoding it accordingly would require some work; let me take a look at it.

Kerry