Primary Key conflict in form entry

[Rosie]

Hi --

I have a main form attached to a table (Invoice header info). When the form loads I use acNewRec to take us to the end of a table, and I get a clean, empty form.

The user, who knows their invoice number, enters it into the first textbox on the form. If there's no match on a separate table (a cross referencing process takes place before this) they get a message that the number wasn't found and they get taken back to rest quietly in the textbox. This is what I want to have happen--they can't go any further.

However, if there is a match with that cross reference table, I want the form to either pull up (this is key) a partially completed invoice header that they they forgot when they went out to lunch, or, if no partially completed invoice header exists, I want them to truck onward and enter it.

Instead, I get a 3022 error saying my action would create duplicates in the primary key and the record cannot be saved. This (I'm guessing) is because of the AcNewRec I invoked in the first place.

This is the query that causes the 3022 error:

strSQL = "SELECT [qEnterHeader].[NewInvoice] FROM qEnterHeader WHERE (((tHeader.NewInvoice)=" & [Forms]![fdataentry]![NewInv] & ")) ;"
Me.RecordSource = strSQL
 

Can someone help sort this out? I would appreciate it.

Thank you.

[Rosie]

Dear someone,

Please take pity on me.

Thank you,
Rosie

[Rabbit]

Hi --

I have a main form attached to a table (Invoice header info). When the form loads I use acNewRec to take us to the end of a table, and I get a clean, empty form.

The user, who knows their invoice number, enters it into the first textbox on the form. If there's no match on a separate table (a cross referencing process takes place before this) they get a message that the number wasn't found and they get taken back to rest quietly in the textbox. This is what I want to have happen--they can't go any further.

However, if there is a match with that cross reference table, I want the form to either pull up (this is key) a partially completed invoice header that they they forgot when they went out to lunch, or, if no partially completed invoice header exists, I want them to truck onward and enter it.

Instead, I get a 3022 error saying my action would create duplicates in the primary key and the record cannot be saved. This (I'm guessing) is because of the AcNewRec I invoked in the first place.

This is the query that causes the 3022 error:

strSQL = "SELECT [qEnterHeader].[NewInvoice] FROM qEnterHeader WHERE (((tHeader.NewInvoice)=" & [Forms]![fdataentry]![NewInv] & ")) ;"
Me.RecordSource = strSQL
 

Can someone help sort this out? I would appreciate it.

Thank you.

I don't understand what you need that code for.

[Rosie]

To attach a recordsource to the form. I've tried a variety of things before and after posting -- that particular scenario was the last I'd tried before posting. Most of the things I've tried are apparently the result of some misguided fantasy I'm having about the relationship between the recordsource and the form, but I don't know what I don't know. If you know what I mean.

This is where I am now: I stopped using anything at the form load event. I have invoices in a database with details table, a header table, and an xref table.

If a user comes to the main form (tied to a query that selects all the records in the header table), and enters an invoice number not on the xref table, they are not allowed to proceed. That works fine.

If the user comes to the main form, and enters an invoice number on the xref table, they can again proceed. That works fine.

If the user comes to the main form and enters an invoice number that is already on the header table, I want the form to display that invoice for editing.

Really, all I want is to know the procedure to pull up a unique recordset for editing.

I'm sorry if I'm incoherent -- I've spent too much time on this now and I can tell I'm not dealing with it rationally. I'm just flailing around. Here's my most recent catastrophe:

 
'IS THERE A HEADER?
'here's where we determine whether the invoice they've entered is already on the table. 
strSQL = "SELECT theader.newinv " & _
"FROM theader " & _
"WHERE (((theader.NewInv)=" & [Forms]![fdataentry]![NewInv] & "));"
rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
If rs.BOF And rs.EOF Then
    MsgBox "No header exists"
Else
'if a header does exist, I want to bring that record up to be displayed for editing
'on the form. Instead I get an error because it clearly thinks I'm trying to create 
'a new record. I'm not. I'm trying to get to the existing record. 
    MsgBox "a header exists; let's do the following"
    Me.RecordSource = strSQL
End If
 

[Rabbit]

I assume invoice number is the primary key?

The problem is that you're using the bound control to determine if there's an existing record. Whatever you type into that control gets saved, so when you type an existing invoice number, it will throw up an error because it is trying to save an existing key.

Try doing it from the Before Update event of the control. If it matches an existing primary key, set cancel to true to stop the update and then go to the existing record.

[Rosie]

Okay. I've tried this (and thank you) and at least it doesn't cough an error back in my face, so it's definitely an improvement. I don't actually know how to go to the record, though. I don't know the syntax to use to perform that function -- how to propel us to that record on the form. Can you tell me what the statement should look like?

For what it's worth, this is all of the code attached now to the textbox. It contains fossils of my previous failures.

 
Private Sub NewInv_BeforeUpdate(Cancel As Integer)
'FIRST, MAKE SURE THE INVOICE EXISTS:
Dim strSQL As String
Me.lstOldInvoices.Requery
strSQL = "SELECT tInvXref.OldInvoice " & _
"FROM tInvXref " & _
"WHERE (((tInvXref.NewInvoice)=" & [Forms]![fdataentry]![NewInv] & "));"
 
Dim Conn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set Conn = CurrentProject.Connection
rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
 
If rs.BOF And rs.EOF Then
    MsgBox NewInv.Text & " not found on Xref table." & _
    vbCr & "You need to allow the system to generate a new invoice first.", vbOKOnly, "Invoice Number not found."
    DoCmd.SetWarnings False
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    DoCmd.SetWarnings True
    Exit Sub
End If
rs.Close
 
'IS THERE A HEADER?
strSQL = "SELECT theader.newinv " & _
"FROM theader " & _
"WHERE (((theader.NewInv)=" & [Forms]![fdataentry]![NewInv] & "));"
rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
If rs.BOF And rs.EOF Then
    MsgBox "No header exists"
Else
    MsgBox "a header exists; let's do the following"
    Cancel = True
    'Me.RecordSource = strSQL
End If
 
rs.Close
Set rs = Nothing
Set Conn = Nothing
End
 
 
Err_handler:
    Select Case Err.Number
        Case "3022"
            DoCmd.SetWarnings False
                MsgBox "A header record already exists for this invoice."
                Unload Me
                DoCmd.SetWarnings False
                Exit Sub
        Case Else
            MsgBox "Error in AllowDataEntry module. Report: " & Err.Number & " " & Err.Description & "."
    End Select
 
 

[Rabbit]

You can use DoCmd.FindRecord. You can get all the syntax and info you need from the microsoft visual basic editor help files.