How do I reference a multi-field Primary Key in VBA?

[David Ball]

Hi Forum,

I have a table with a multifield primary key, made up of the fields "Plant" and "Code". Plant is not unique and neither is Code, but both combined provide a unique key.

In the VBA code below I have referenced "Plant" (see the part of the code that I have highlighted in magenta).

When I select a record in the Form and run the code it returns a record with the correct Plant, but usually the wrong Code. I realise that this is probably because I require both Plant and Code to provide a unique record for Access to find.
I don't know how to show a multi-field Primary Key in my code.

Can anyone help, please?

Thanks very much.

Dave

Public Function MergetoWordQRY()
'This method creates a new document in MS Word
'using Automation
On Error Resume Next
Dim rsCert As Recordset, iTemp As Integer
Dim WordObj As Word.Application
Set rsCert = DBEngine(0).Databases(0).OpenRecordset("tblComplCert", dbOpenTable)

rsCert.Index = "PrimaryKey"
rsCert.Seek "=", Forms!frmByClientSig![Plant]

If rsCert.NoMatch Then
MsgBox "Invalid record dude", vbOKOnly
Exit Function
End If
DoCmd.Hourglass True
Set WordObj = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set WordObj = CreateObject("Word.Application")
End If
WordObj.Visible = True
WordObj.Documents.Add ("C:\Access Automation\Test.doc")
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Plant"
WordObj.Selection.TypeText rsCert![Plant]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Area"
WordObj.Selection.TypeText rsCert![Area]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Code"
WordObj.Selection.TypeText rsCert!

[/FONT][/SIZE]
[SIZE=3][FONT=Arial] WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="PrjSig"[/FONT][/SIZE]
[SIZE=3][FONT=Arial] WordObj.Selection.TypeText rsCert![ProjSignatory] [/FONT][/SIZE]
[SIZE=3][FONT=Arial]  WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Desc"[/FONT][/SIZE]
[SIZE=3][FONT=Arial] WordObj.Selection.TypeText rsCert![Description] [/FONT][/SIZE]
[SIZE=3][FONT=Arial]  WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Act1"[/FONT][/SIZE]
[SIZE=3][FONT=Arial] WordObj.Selection.TypeText rsCert![Action1] [/FONT][/SIZE]
[SIZE=3][FONT=Arial]  WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Act2"[/FONT][/SIZE]
[SIZE=3][FONT=Arial] WordObj.Selection.TypeText rsCert![Action2][/FONT][/SIZE]
[SIZE=3][FONT=Arial]  WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Doc1"[/FONT][/SIZE]
[SIZE=3][FONT=Arial] WordObj.Selection.TypeText rsCert![Doc1] [/FONT][/SIZE]
[SIZE=3][FONT=Arial] Set WordObj = Nothing[/FONT][/SIZE]
[SIZE=3][FONT=Arial] DoCmd.Hourglass False[/FONT][/SIZE]
[SIZE=3][FONT=Arial] Exit Function[/FONT][/SIZE]
[SIZE=3][FONT=Arial]TemplateError:[/FONT][/SIZE]
[SIZE=3][FONT=Arial] Set WordObj = Nothing[/FONT][/SIZE]
[SIZE=3][FONT=Arial] Exit Function[/FONT][/SIZE]
[SIZE=3][FONT=Arial]End Function[/FONT][/SIZE]

 

[apr pillai]

rsCert.Index = "PrimaryKey"
rsCert.Seek "=", Forms!frmByClientSig![Plant]

You may use the following method:

Dim Key1, Key2

Key1 = Forms!frmByClientSig![Plant]
Key2 = Forms!frmByClientSig![OtherField]

rsCert.Index = "PrimaryKey"
rsCert.Seek "=", Key1, Key2

 

[David Ball]

Thank you very much.

I really appreciate your help.

Dave