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

 

[KerryL]

Bryan,
I added your code to a cmd on frmSel in the test DB you emailed me and it worked like a charm. Added a Smith and a Jones into the new table with no problem.

Unfortunately, when I tried using the exact same code for a cmd button in my DB (on frmSelPartic), I received the following error (at "Dim Db as Database):

Compile error:
User-defined type not defined

 

[Lightning]

Add a reference to the DAO 3.6 Object library to your DB.

 

[KerryL]

Bryan,

I just wanted to let you know that I was finally able to get both steps working. I changed the form where the selection takes place by replacing the listbox with a form containing the data in tabular form, and that allowed me to open the 2nd form with the selected record.

Then I inserted the code you suggested behind a command button and modified it to match my fields. I'm getting a "type mismatch" error now, so I'll have to check for field matches between the primary table and the one I'm sending the data to, but I think I'm on track now.

Thanks very much for all of your help!

Kerry

 

[KerryL]

Bryan,

I'm sorry to bother you again, but I can't seem to eliminate this "type mismatch" error I keep getting.

I checked both tables and the field types match, so I'm not trying to transfer text to a numerical field, etc. I even commented out all the lines except for the SSN to see if I could transfer one field from table to table but to no avial.

Are there more possible reasons for a type mismatch error than just mismatched fields and data? I don't know why else I'd be getting the error, and nothing jumped out at me in the help files.

TIA,
kerry

 

[PHV]

Any chance you may grab a Null value ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Lightning]

Have you stepped through the code to see where the error occurs? If so, can you post the code and the error message, and highlight which line of code is causing the problem?

HTH
Lightning

 

[sterobw]

Lightning,

Thanks for the great info. I need a way to copy "LastName" and "FirstName" from "tblEmpInfo" (has all emp info entered) to "tblEmp" which is an empty table. I copied "tblEmpInfo" from another db. How can I modify the above code to automatically populate my blank "tblEmp"? I know this is basic but am in a time crunch (aren't we al??)

Any help would be appreciated!

Steve

 

[Lightning]

Sterobw
Do you need to copy the records one at a time or a group together?

If one at a time,

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("tblEmpInfo", dbOpenTable)
    With Rs
        .AddNew
        .Fields("LastName") = Me.LastName
        .Fields("Firstname") = Me.FirstName
        .Update
    End With
    Rs.Close
    Set Rs = Nothing
End Sub

Remember that this code needs a Reference to the DAO 3.6 Object Library (use Tools|References in any Module window to set the reference).

If you need to do more than one at a time, or the whole table, try an Append Query that selects criteria from your form (you are doing this from a form, aren't you?).


HTH
Lightning

 

[KerryL]

I'm moving them one at a time, just like you suggested in the code you shared. Here's what my code looks like:

[begin code]
Dim Db As Database
Dim Rs As Recordset

Set Db = CurrentDb
Set Rs = Db.OpenRecordset("tblParticipant", dbOpenTable)
With Rs
.AddNew
.Fields("Participant Name") = Me.txtPartName
.Fields("Soc Sec #") = Me.txtSSN
.Fields("Case Mstr ID") = Me.txtCaseMstrID
.Fields("Application Date") = Me.txtApplDate
.Fields("Closure Date") = Me.txtClosDate
.Fields("Caseload #") = Me.txtCaseNo
.Fields("Caseload Assignment") = Me.txtAssign
.Fields("Notes") = Me.txtNotes
.Fields("Ret Case") = Me.chkRet
.Fields("Other") = Me.chkOther
.Update
End With
Rs.Close
Set Rs = Nothing
[end code]

FYI, I put a stop in the code at Set Db = CurrentDb and the "type mismatch" error occurs as soon as it hits this line: Set Rs = Db.OpenRecordset("tblParticipant", dbOpenTable)

So the error is occurring before I even get to the code that moves the data. I'm still troubleshooting, but if you have any ideas please let me know. Thank you.

KerryL

 

[Lightning]

Kerry

I took your code above and copied it into my sample Db. It ran without any problems whatsoever. No Type Mismatch error occurred.

Can you please post the following information:

the Field Type of each field in your original table.
the Field Type of each field in your destination table. what References are checked for your DB

Don't worry - we'll get there yet!

Lightning

 

[PHV]

Replace this:
Dim Rs As Recordset
By this:
Dim Rs As DAO.Recordset

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KerryL]

The advice from PH worked.

I had already checked the box for the DAO 3.6 Reference (Tools/Utilities) and once I modified the opening line to this it worked fine:

Dim Rs As DAO.Recordset

I'm getting an error now because when adding the participant info to Table 2, it requires a link to another table it's linked to, but I'll get that figured out. I figure I just need to add a value into the linked field and that isn't happening right now.

Thanks very much to both of you!