Selecting 1 record from multiple to populate Main Form

[Ian Mac]

All,

I have a Main Form where the user enters a value in a Textbox and then hits a Button to return the data.
I have recently found out that there may be 2 or more records with the same Value in that field.

So now I have introduced a method to open a Form with any examples where there are more than 1 record, as follows:

Private Sub cmb_SearchRec_Click()

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb

SetNINOval (Me!NinoCrn.Value)

Set rst = dbs.OpenRecordset("qry_DC:Main")


'Application.Echo False

If rst.RecordCount = 0 Then
        If NINOval = "" Then
            Me!NinoCrn.SetFocus
            Exit Sub
                Else
            MsgBox "The NINO you have enter does not exist on the System," & _
            Chr(13) & Chr(10) & "please re-enter the NINO again", vbCritical + vbOKOnly, "Error"
            Me!NinoCrn.Value = ""
            Me!NinoCrn.Requery
            Me!NinoCrn.SetFocus
            Exit Sub
        End If

Else
rst.MoveLast
If rst.RecordCount > 1 Then
        MsgBox "There are " & rst.RecordCount & " records with this NINO", vbOKOnly, "Choose Record"
        DoCmd.OpenForm "frm_MultiRecords"
        Exit Sub
End If

Me.Requery........................etc. If only one record.

This opens up the new Form:

The design is:

Control Source = qry_DC:Main
Continuous Forms
Header = Lables
Detail = Textboxes with values
No Additions

How can I modify the above so the user selects the record required which inturn populates the Main Form with the correct record.
I was thinking of adding a simple Checkbox but I don't know how to add one for each record in the set.

I realise I will doubtless need to provide more info, but that's my starter for 10.

Cheers,

 

[trucktime]

Populate

How about using a Combo Box, you as can ad as many fields as needed to identify the record you want.
And then set the bookmark in the After Update Event of the combobox to
populate the form.

 

[Ian Mac]

How about using a Combo Box, you as can ad as many fields as needed to identify the record you want.
And then set the bookmark in the After Update Event of the combobox to
populate the form.

Cheers,

I was orginally going to go with that idea, however:

a) Combobox 1 will have more than the 65536 limit as it's from a table with 300,000+ records
b) I have a lot of code behind the button which does many calculations in the background, I'd rather leave it as a button for more control.
c) I'm not using bookmarks/recordsets for the actual population of the form, I'm using a Query to buffer the information and help with record locking, I'm just using RecordSet for counting the Query test.
Also, it's working REALLY fast and I don't want to tinker, 300,000 Main table records, 8 additional Tables and 13 subforms/queries in well under a second across the network.
c) aesthetically it is far nicer for the user to hit a button as out of 300,000+ records there are currently only 900 duplicate records so it's not going to be an action that's required very often.

I'd rather have them select just that record and pass it back to the Main form.

If there are other ways I'll certainly listen.

Attached is what I currently have.

Regards,