writing primary key when multiple fields

[Guest]

I need to write the name of my primary key in some code but my primary key is
made up of three combined fields. file_number, product_type and order_number

How do I write this in code...with a semi colon between them like this...

file_number; product_type; order_number

Thanks for any assistance

 

[John Spencer]

Try this NOT FULLY TESTED function.

Public Function fGetPrimaryIndexFields(strTableName As String) As String
'=========================================================================
' Procedure : fGetPrimaryIndexFields
' Created : 2/28/2007 08:34
' Author : John Spencer
' Purpose : Return name of the primary key index and field(s) used
' Arguments : Name of a table in the database
'=========================================================================

Dim dbAny As DAO.Database
Dim tblAny As DAO.TableDef
Dim idxAny As DAO.Index
Dim I As Integer
Dim strReturn As String: strReturn = "None"

Set dbAny = CurrentDb()
Set tblAny = dbAny.TableDefs(strTableName)

For Each idxAny In tblAny.Indexes
If idxAny.Primary = True Then
strReturn = idxAny.Name & ": Fields "
For I = 0 To idxAny.Fields.Count - 1
strReturn = strReturn & idxAny.Fields(I).Name & ","
Next I
strReturn = Left(strReturn, Len(strReturn) - 1)
Exit For
End If

Next idxAny
fGetPrimaryIndexFields = strReturn
End Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thank you for the reply. I suspect that I didn't word my question right. I
have been directed to a simple bit of code to call up a recordset from a
subform and put in on a form. The code is as follows:

Private Sub cmdPrint_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[ID] = " & Me.[ID]
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End If
End Sub

My problem is that I have to substitute the name of my primary key for "ID"
in the above code but my primary key is three combined fields. I don't know
how to write that into the code.

Thank you for reading this far.

Derek (Slappy)

 

[Tim Ferguson]

strWhere = "[ID] = " & Me.[ID]

My problem is that I have to substitute the name of my primary key for
"ID" in the above code but my primary key is three combined fields. I
don't know how to write that into the code.

strWhere = "FirstField = " & Me.TextBox1 & " " & _
"AND SecondField = " & Me.TextBox2 & " " & _
"AND ThirdField = " & Me.TextBox3


If the values are not all numeric, then remember to put the appropriate
quotes or date formats around them.

Hope that helps



Tim F

 

[Guest]

Thank you very much!

strWhere = "[ID] = " & Me.[ID]

My problem is that I have to substitute the name of my primary key for
"ID" in the above code but my primary key is three combined fields. I
don't know how to write that into the code.

strWhere = "FirstField = " & Me.TextBox1 & " " & _
"AND SecondField = " & Me.TextBox2 & " " & _
"AND ThirdField = " & Me.TextBox3


If the values are not all numeric, then remember to put the appropriate
quotes or date formats around them.

Hope that helps



Tim F

 

[Guest]

Sorry to bother again. I tried this in my code and it gives me error
messages. The overall code is as follows (my three fields that combine to
make the primary key are file_number, record_number and request_number - they
are all numerical):


Private Sub Command35_Click()
Dim strWhere As String
If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If
If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "FirstField = " & Me.file_number &" "&_
"AND SecondField = " & Me.record_number&" "&_
"AND ThirdField = " & Me.request_number
DoCmd.OpenReport "RequestCNRs", acViewPreview, , strWhere
End If
End Sub

Thank you very much!

strWhere = "[ID] = " & Me.[ID]

My problem is that I have to substitute the name of my primary key for
"ID" in the above code but my primary key is three combined fields. I
don't know how to write that into the code.

strWhere = "FirstField = " & Me.TextBox1 & " " & _
"AND SecondField = " & Me.TextBox2 & " " & _
"AND ThirdField = " & Me.TextBox3


If the values are not all numeric, then remember to put the appropriate
quotes or date formats around them.

Hope that helps



Tim F

 

[Tim Ferguson]

Sorry to bother again. I tried this in my code and it gives me error
messages. The overall code is as follows (my three fields that
combine to make the primary key are file_number, record_number and
request_number - they are all numerical): ....
strWhere = "FirstField = " & Me.file_number &" "&_
"AND SecondField = " & Me.record_number&" "&_
"AND ThirdField = " & Me.request_number

I strongly suspect that the fields in the table are not called
FirstField, SecondField etc.

The string that you are building should end up looking like a WHERE
clause in SQL (except, confusingly, without the WHERE itself), i.e

"File_Number = 12 AND Record_Number = 14 AND Request_Number = 18"

if that is what the fields are called.

I would recommend putting a breakpoint either on this line or the next
one and checking deliberately that it is what you expect.

Hope that helps


Tim F