Concatenate Serial # and Part # to create value for key field

[Ruth]

I have a form that collects information for a after-market part to be
built. There are two combo boxes-- the first lists the Original Mfg.
Name, which filters the second box that lists the part numbers for
that mfg. The third is a text box where a serial number is entered.
The combination of the OEM part number (PN) and the aftermarket
producer's Serial Number (SN) make up the PartID. There are some other
information fields as well.

I created an append query that concatenates the PN and SN fields to
CellID which becomes the Key field in the primary table. I don't want
to see the append message each time, but if I turn off warnings, you
don't see the error message when the update fails. I don't think the
Dcount is working because the message box doesn't activate.

Here's the code I have now:
Private Sub btnAddCell_Click()
On Error GoTo btnAddCell_Err

' Turn Off Screen Display while code runs
DoCmd.Echo False, "Please wait while new cell is created "
' Turns on hourglass
DoCmd.Hourglass True
' Turns warning off
DoCmd.SetWarnings False
' Saves new record
DoCmd.RunCommand acCmdSaveRecord
'Checks for duplicate values that with make up Cell ID
If DCount("*", "[tblCellEntry]", [PN] = """ & Me.PN & """ And [SN]
= """ & Me.SN & """) = 0 Then
' RunsQuery to Add New Cell
DoCmd.OpenQuery "qryAddNewCell", acViewNormal, acEdit
' Closes append query
DoCmd.Close acQuery, "qryAddNewCell"
' Closes New Cell Form
DoCmd.Close acForm, "frmNewCell"
' Turns off hourglass
DoCmd.Hourglass False
' Turns warnings back on
DoCmd.SetWarnings True
' Activates Screen display again
DoCmd.Echo True, ""
Else
MsgBox "A cell already exists with this S/N. Choose a new S/N
or return to the Main Menu and Edit Existing Cell",
(vbDefaultButton1), "Duplicate S/N"
End If

btnAddCell_Exit:
Exit Sub

btnAddCell_Err:
MsgBox "A cell already exists with this S/N. Choose a new S/N or
return to the Main Menu and Edit Existing Cell", (vbDefaultButton1),
"Duplicate S/N"
Resume btnAddCell_Exit
End Sub.

I'm sure there's a glaring error there somewhere, but I'm not finding
it!

Other ideas for how to accomplish this?

Many thanks in advance,
Ruth

 

[RoyVidar]

I have a form that collects information for a after-market part to be
built. There are two combo boxes-- the first lists the Original Mfg.
Name, which filters the second box that lists the part numbers for
that mfg. The third is a text box where a serial number is entered.
The combination of the OEM part number (PN) and the aftermarket
producer's Serial Number (SN) make up the PartID. There are some
other information fields as well.

I created an append query that concatenates the PN and SN fields to
CellID which becomes the Key field in the primary table. I don't want
to see the append message each time, but if I turn off warnings, you
don't see the error message when the update fails. I don't think the
Dcount is working because the message box doesn't activate.

Here's the code I have now:
Private Sub btnAddCell_Click()
On Error GoTo btnAddCell_Err

' Turn Off Screen Display while code runs
DoCmd.Echo False, "Please wait while new cell is created "
' Turns on hourglass
DoCmd.Hourglass True
' Turns warning off
DoCmd.SetWarnings False
' Saves new record
DoCmd.RunCommand acCmdSaveRecord
'Checks for duplicate values that with make up Cell ID
If DCount("*", "[tblCellEntry]", [PN] = """ & Me.PN & """ And
[SN] = """ & Me.SN & """) = 0 Then
' RunsQuery to Add New Cell
DoCmd.OpenQuery "qryAddNewCell", acViewNormal, acEdit
' Closes append query
DoCmd.Close acQuery, "qryAddNewCell"
' Closes New Cell Form
DoCmd.Close acForm, "frmNewCell"
' Turns off hourglass
DoCmd.Hourglass False
' Turns warnings back on
DoCmd.SetWarnings True
' Activates Screen display again
DoCmd.Echo True, ""
Else
MsgBox "A cell already exists with this S/N. Choose a new S/N
or return to the Main Menu and Edit Existing Cell",
(vbDefaultButton1), "Duplicate S/N"
End If

btnAddCell_Exit:
Exit Sub

btnAddCell_Err:
MsgBox "A cell already exists with this S/N. Choose a new S/N or
return to the Main Menu and Edit Existing Cell", (vbDefaultButton1),
"Duplicate S/N"
Resume btnAddCell_Exit
End Sub.

I'm sure there's a glaring error there somewhere, but I'm not finding
it!

Other ideas for how to accomplish this?

Many thanks in advance,
Ruth

My view, is that I think you should rethink your approach.

You should not concatenate (or calculate) values into one field, that
can be derived from others. This will come back an bite you - hard!

In stead, you should either create one primery key consisting of those
fields (I think a primary key of a table can consist of as many as
10 fields) or create a unique index over those fields, and perhaps
have a meaningsless primary key for that table (Autonumber?).

 

[Ruth]

I have a form that collects information for a after-market part to be
built. There are two combo boxes-- the first lists the Original Mfg.
Name, which filters the second box that lists the part numbers for
that mfg. The third is a text box where a serial number is entered.
The combination of the OEM part number (PN) and the aftermarket
producer's Serial Number (SN) make up the PartID. There are some
other information fields as well.

I created an append query that concatenates the PN and SN fields to
CellID which becomes the Key field in the primary table. I don't want
to see the append message each time, but if I turn off warnings, you
don't see the error message when the update fails. I don't think the
Dcount is working because the message box doesn't activate.

Here's the code I have now:
Private Sub btnAddCell_Click()
On Error GoTo btnAddCell_Err

    ' Turn Off Screen Display while code runs
    DoCmd.Echo False, "Please wait while new cell is created "
    ' Turns on hourglass
    DoCmd.Hourglass True
    ' Turns warning off
    DoCmd.SetWarnings False
    ' Saves new record
    DoCmd.RunCommand acCmdSaveRecord
    'Checks for duplicate values that with make up Cell ID
    If DCount("*", "[tblCellEntry]", [PN] = """ & Me.PN & """ And
[SN] = """ & Me.SN & """) = 0 Then
        ' RunsQuery to Add New Cell
        DoCmd.OpenQuery "qryAddNewCell", acViewNormal, acEdit
        ' Closes append query
        DoCmd.Close acQuery, "qryAddNewCell"
        ' Closes New Cell Form
        DoCmd.Close acForm, "frmNewCell"
        ' Turns off hourglass
        DoCmd.Hourglass False
        ' Turns warnings back on
        DoCmd.SetWarnings True
        ' Activates Screen display again
        DoCmd.Echo True, ""
Else
        MsgBox "A cell already exists with this S/N. Choose a new S/N
or return to the Main Menu and Edit Existing Cell",
(vbDefaultButton1), "Duplicate S/N"
    End If

btnAddCell_Exit:
    Exit Sub

btnAddCell_Err:
    MsgBox "A cell already exists with this S/N. Choose a new S/N or
return to the Main Menu and Edit Existing Cell", (vbDefaultButton1),
"Duplicate S/N"
    Resume btnAddCell_Exit
End Sub.

I'm sure there's a glaring error there somewhere, but I'm not finding
it!

Other ideas for how to accomplish this?

Many thanks in advance,
Ruth

My view, is that I think you should rethink your approach.

You should not concatenate (or calculate) values into one field, that
can be derived from others. This will come back an bite you - hard!

In stead, you should either create one primery key consisting of those
fields (I think a primary key of a table can consist of as many as
10 fields) or create a unique index over those fields, and perhaps
have a meaningsless primary key for that table (Autonumber?).

Roy,
I know it's not the best method. :-/ Here is my limitation: the
client wants to use a hand held scanner for shop workers to scan the
ID and open so they can enter details in a production log during mfg.
process. Since they haven't started barcoding yet, I had the thought
that I could tell them to barcode the two numbers (P/N and S/N)
separately and provide two fields for scanning.

I just hate the added level of complexity when your key ID consists of
more than one field. I'm one level above noob in coding.

Any other ideas?

Thanks for your reply!
Ruth

 

[Ruth]

I did a little more research on the barcode process and I can specify
that a tab character is coded between the S/N and P/N. This would
accomplish entering the data in one scan, but separate the value into
the two fields. I think I'm going to go that route.

Thanks for the guidance!