Concatenate field as PK

[sebastico]

Dear all
I have the Table SigNum with the fields:
CodSig (PK) (string)
Prov (string)
Cant (string)
Dist (string)
Bloq (string)
Sec (string)
NumPar (string)


The PK must be the combination of fields Prov, Cant, Dist, Bloq, Sec NumPar
I have the code:

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 = "FirstField = " & Me.TextBox1 & " " & _
"AND SecondField = " & Me.TextBox2 & " " & _
"AND ThirdField = " & Me.TextBox3 & " " & _
"AND FourthField = " & Me.TextBox4 & " " & _
"AND FifthField = " & Me.TextBox5 & " " & _
"AND SixthField = " & Me.TextBox6 & " " & _

DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End If
End Sub

However, in the form (based on the Table SigNum) when I type a value in the
fields the PK field does not display it. I need that when I type a value in a
field, the PK field automatically display it. How do I do this?

Help please
Many thanks in advance.

 

[Steve]

You can make it easy on yourself by adding a field named SigNumID to your
table and making it autonumber. It will self-populate when you add the field
and will self-populate when you add new records.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[sebastico]

Dear Steve
Many thanks for your kindness. However, my CodSig must be the concatenation
of fields: Prov, Cant, Dist, Bloq, Sect and NumParc.
Have you got another sugg.

 

[Bob Quintal]

Dear all
I have the Table SigNum with the fields:
CodSig (PK) (string)
Prov (string)
Cant (string)
Dist (string)
Bloq (string)
Sec (string)
NumPar (string)


The PK must be the combination of fields Prov, Cant, Dist, Bloq,
Sec NumPar I have the code:

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 = "FirstField = " & Me.TextBox1 & " " & _
"AND SecondField = " & Me.TextBox2 & " " & _
"AND ThirdField = " & Me.TextBox3 & " " & _
"AND FourthField = " & Me.TextBox4 & " " & _
"AND FifthField = " & Me.TextBox5 & " " & _
"AND SixthField = " & Me.TextBox6 & " " & _

DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End If
End Sub

However, in the form (based on the Table SigNum) when I type a
value in the fields the PK field does not display it. I need that
when I type a value in a field, the PK field automatically display
it. How do I do this?

Help please
Many thanks in advance.

You can and should simply set the primary key to the combination of
the fields, not copy them to the CodSig field, which is unnecessary
and redundant. Open the table in design view, click on the Prov
recordselector, then shift click on the other fields. Now click on
the Key icon in the toolbar.

In regards to your code, are FirstField, SecondField, Thirdfield
the names of your fields or are Prov, Cant, Dist...????

If the names are Prov, Cant etc, .your code should use them instead
of Firstfield, SecondField etc. You also need, since the field types
are string, per your example, you need to add quotation marks into
the strwhere.

strWhere = "Prov = """& me.textbox1 & """ " & _
"And Cant = """ & me.textbox2 & """ " & _

 

[Douglas J. Steele]

WHY does the PK have to be a combination of those fields? Access allows you
to specify up to 10 separate fields in a single index. That means that your
PK could be those 6 fields unconcatenated.

Drop CodSig from your table. Make the PK the 6 separate fields. If you
really need the concatenated field, create a query and concatenate the 6
fields into a single calculated field. Use the query wherever you would
otherwise have used the table.

 

[sebastico]

Dear Doug
The reason is a requirement in a project I am in charge. Let me see if a
can explain what I need. Iam sorry English is not my morther tongue.
When I type a value in field Prov the value must be displayed in CodSig,
when I type a value in Cant field the value must be displayed next to Prov
in the CodSIg an so on.

Many thanks for your sugg. If my idea for technical reason can not be
possible I will try your advise.

Many thanks

 

[Steve]

Obviously you need a CodSig field on your form.

Add the following fumction to your form:
Function CodSigCat() As String
CodSigCat = Me!Prov & ","
CodSigCat = CodSigCat & Me!Cant & ","
CodSigCat = CodSigCat & Me!Dist & ","
CodSigCat = CodSigCat & Me!Bloq & ","
CodSigCat = CodSigCat & Me!Sect& ","
CodSigCat = CodSigCat & Me!NumParc
End Function

Put the following code in the AfterUpdate event of your six fields:
Me!CodSig = CodSigCat()

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John Marshall, MVP]

That is a very long winded way of doing,

CodSigCat = Me!Prov & "," & Me!Cant & "," & Me!Dist & "," & Me!Bloq & "," &
Me!Sect & "," & Me!NumParc

John... Visio MVP

PS: You also missed a space between "!SECT" and "&"

 

[Douglas J. Steele]

Try the query approach I suggested. I think you'll find that you will end up
with the concatenated CodSig field.

Your alternative is to put code in the AfterUpdate of each of the 6 fields
on your form so that you create the concatenated value and store it in the
field. Note, though, that this is actually a violation of database
normalization principles, since you would be storing data redundantly.

 

[sebastico]

Many thanks, your sugg works great. It makes easy and safe data typing in
the form. Seems to be very simple. The use of Me! is amazing.
One more little help if possible. Could you tell me the meaning of Me!,
&",". how to use it. Could you suggest me where I can get information to
learn more about the use of Functions in a form.
Thanks again