recordset clone and multiple keys (1 Viewer)

[groengoen]

I have a form based on a query with multiple primary keys. I have Company, MemberNumber and DateVisit as the 3 keys. I want to use a combo box to select a member and have been able to do it with the code below, which loops through every record to check whether the selected on exists and then bookmarks it. I would rather use an SQL statement to select the required record, but that doesn't seem to be available with RecordsetClone, and FindFirst only caters for a single primary key, or am I wrong.

    Dim NumRec As Integer
    Dim strSQL As String
    Dim response As Boolean
    Dim vntPosition As Variant
    'Declare and instantiate a recordset
    Dim rst As Recordset
    Set rst = Me.RecordsetClone

    'Establish the connection and cursor type,
    'and open the recordset
    rst.MoveFirst
    Do Until rst.EOF
        wsCompany = rst("Company")
        wsMemberNumber = rst("MemberNumber")
        wsDateVisit = rst("DateVisit")
        If wsDateVisit = lstClinicVisit.Column(0) _
        And wsMemberNumber = lstClinicVisit.Column(2) _
        And wsCompany = lstClinicVisit.Column(1) _
        Then
            'Record found for this member for this day
            'Store details for Edit mode
            Me.DateVisit.SetFocus
            Me.Bookmark = rst.Bookmark
            rst.Close
            Set rst = Nothing
            Exit Sub
        Else
            rst.MoveNext
        End If
    Loop

    'No records found for this member
    'Clear details for Edit Mode
    MsgBox "No record found for this member for this day! Try again or use scroll keys"
    Me.MemberNumber = Null
    Me.Company = Null
    Me.DateVisit = Null
    Me.DateGP = Null
    Me.txtName = ""
    Me.txtAddress = ""
    Me.Scan = Null
    Me.Condition = Null
    Me.Outcome = Null
    Me.Investigation = Null
    Me.Charge = Null
    Me.DateVisit.SetFocus
    rst.Close
    Set rst = Nothing

 

[dcb]

Why are you using recordsetClone as you already know what records are there in your original query?

 

[boblarson]

1. This illustrates why I always argue for using a SURROGATE key (Autonumber) and then use an INDEX to keep duplicates from happening for certain data.

2. Then you can use the primary key for a search.

Composite keys are a pain in the butt, in my opinion, to use and maintain.

Anyway groengoen, also to correct your verbage a bit, you do not have multiple primary keys. You have ONE primary key. A table can ONLY have one primary key. Your primary key is a composite key which is made up from three fields, but it is still only one primary key.

 

[groengoen]

Why are you using recordsetClone as you already know what records are there in your original query?

I just want to go to a particular record in the form quickly in a listbox (I meant listbox rather than combobox)

 

[groengoen]

1. This illustrates why I always argue for using a SURROGATE key (Autonumber) and then use an INDEX to keep duplicates from happening for certain data.

2. Then you can use the primary key for a search.

Composite keys are a pain in the butt, in my opinion, to use and maintain.

Does this mean that there is no quick fix for the existing situation?

I stand correct in my terminology.

 

[groengoen]

oops I mean I stand corrected!

 

[boblarson]

You could create a concatenated field in the query with all three and then use that as the search.

 

[dcb]

Bob's way is right and first choice
however you could populate your listbox on load by cycling through and getting
rst.AbsolutePosition
and then use
rst.Move
[listBoxAbsolutePositionKey],BOF

just throwing an Idea

 

[dcb]

You could create a concatenated field in the query with all three and then use that as the search.

As long as DateVisit is full date and time otherwise you could still get a duplicate

 

[gemma-the-husky]

it may be worth considering the structure here a bit

the key think is you are entering a date which may or may not be there

so a normal procedure here is to use cascading combo boxes

a) first have a combo box to pick the member

b) then have another combo box to show the dated vistis of that member

c) then pick the one you want - or show them all in a continuous form -

you dont want to be trying to guess a date that may not be in the file at all - databases have lots of facilties that you can use to help you, not make it harder.

 

[groengoen]

Sorry for the delay! I tried Bob's suggestion on concatenating:

KeyExpr: [KeyConcat]=[DateVisit] & [Company] & [MemberNumber]

and the following code:

    Dim strSQL As String
    Dim rst As Recordset
    Set rst = Me.RecordsetClone
    strSQL = "KeyExpr = " & lstClinicVisit.Column(0) & _
            lstClinicVisit.Column(1) & _
            lstClinicVisit.Column(2)
    
    rst.FindFirst (strSQL)

    If Not rst.EOF Then
        Me.Bookmark = rst.Bookmark
    Else
        'No records found for this member
        'Clear details for Edit Mode
        MsgBox "No record found for this member for this day! Try again or use scroll keys"
        Me.MemberNumber = Null
        Me.Company = Null
        Me.DateVisit = Null
        Me.DateGP = Null
        Me.txtName = ""
        Me.txtAddress = ""
        Me.Scan = Null
        Me.Condition = Null
        Me.Outcome = Null
        Me.Investigation = Null
        Me.Charge = Null
        Me.DateVisit.SetFocus
        rst.Close
        Set rst = Nothing
        Exit Sub
    End If
    rst.Close
    Set rst = Nothing

In testing strSQL had this value "20/09/2009212345" which was the
DateVisit =20/09/2009,
Company = 1
MemberNumber = 12345.

The FindFirst threw up :
rst("DateVisit") = 07/09/2009
rst("Company") = 2
rst("MemberNumber") = 400

I previously had a problem with a SQL date comparison and had to use DateDiff to get around it as apparently Access spuriously converts UK dates to US dates in SQL. I think this may have happened here and scuppers this idea.

 

[dcb]

Grab the date as a string then it will keep the regional settings
If you dont want the "/" then use DatePart

KeyConcat: "DV_" & DatePart("d",[DateVisit]) & DatePart("m",[DateVisit]) & DatePart("yyyy",[DateVisit]) & "_CO_" & [Company] & "_MN_" & [MemberNumber]

Using your example this should give you
DateVisit =20/09/2009,
Company = 1
MemberNumber = 12345

DV_20092009_CO_1_MN_12345

Each to there own but if you making a con key you might as well give yourself some indicators of where the data has come from

should you want to use the dates as you are then use Cstr(DateVisit) the conversion to string will keep the regional settings - I know the pain with the UK date format!!!

Enough of that.......

The code you are using confuses me :

strSQL = "KeyExpr = " & lstClinicVisit.Column(0) & _
lstClinicVisit.Column(1) & _
lstClinicVisit.Column(2)

rst.FindFirst (strSQL)

I would change your listbox rowsource to look something like your form source -
Form has:
concatenated_field, DateVisit,Company,MemberNumber,data,data,data,etc
ListBox should have:
concatenated_field, DateVisit,Company,MemberNumber

Thus simplifying the code to :

strSQL = "KeyExpr = " & Me.lstClinicVisit

Using this it wouldnt matter what Access did with the date they should both match as the keys are the same