By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,249 Members | 1,853 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,249 IT Pros & Developers. It's quick & easy.

Primary Key conflict in form entry

P: 6
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:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT [qEnterHeader].[NewInvoice] FROM qEnterHeader WHERE (((tHeader.NewInvoice)=" & [Forms]![fdataentry]![NewInv] & ")) ;"
  2. Me.RecordSource = strSQL
  3.  
Can someone help sort this out? I would appreciate it.

Thank you.
Mar 27 '07 #1
Share this Question
Share on Google+
6 Replies


P: 6
Dear someone,

Please take pity on me.

Thank you,
Rosie
Mar 28 '07 #2

Rabbit
Expert Mod 10K+
P: 12,427
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:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT [qEnterHeader].[NewInvoice] FROM qEnterHeader WHERE (((tHeader.NewInvoice)=" & [Forms]![fdataentry]![NewInv] & ")) ;"
  2. Me.RecordSource = strSQL
  3.  
Can someone help sort this out? I would appreciate it.

Thank you.
I don't understand what you need that code for.
Mar 28 '07 #3

P: 6
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 'IS THERE A HEADER?
  3. 'here's where we determine whether the invoice they've entered is already on the table. 
  4. strSQL = "SELECT theader.newinv " & _
  5. "FROM theader " & _
  6. "WHERE (((theader.NewInv)=" & [Forms]![fdataentry]![NewInv] & "));"
  7. rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
  8. If rs.BOF And rs.EOF Then
  9.     MsgBox "No header exists"
  10. Else
  11. 'if a header does exist, I want to bring that record up to be displayed for editing
  12. 'on the form. Instead I get an error because it clearly thinks I'm trying to create 
  13. 'a new record. I'm not. I'm trying to get to the existing record. 
  14.     MsgBox "a header exists; let's do the following"
  15.     Me.RecordSource = strSQL
  16. End If
  17.  
Mar 28 '07 #4

Rabbit
Expert Mod 10K+
P: 12,427
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.
Mar 28 '07 #5

P: 6
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub NewInv_BeforeUpdate(Cancel As Integer)
  3. 'FIRST, MAKE SURE THE INVOICE EXISTS:
  4. Dim strSQL As String
  5. Me.lstOldInvoices.Requery
  6. strSQL = "SELECT tInvXref.OldInvoice " & _
  7. "FROM tInvXref " & _
  8. "WHERE (((tInvXref.NewInvoice)=" & [Forms]![fdataentry]![NewInv] & "));"
  9.  
  10. Dim Conn As ADODB.Connection
  11. Dim rs As New ADODB.Recordset
  12. Set Conn = CurrentProject.Connection
  13. rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
  14.  
  15. If rs.BOF And rs.EOF Then
  16.     MsgBox NewInv.Text & " not found on Xref table." & _
  17.     vbCr & "You need to allow the system to generate a new invoice first.", vbOKOnly, "Invoice Number not found."
  18.     DoCmd.SetWarnings False
  19.     DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
  20.     DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
  21.     DoCmd.SetWarnings True
  22.     Exit Sub
  23. End If
  24. rs.Close
  25.  
  26. 'IS THERE A HEADER?
  27. strSQL = "SELECT theader.newinv " & _
  28. "FROM theader " & _
  29. "WHERE (((theader.NewInv)=" & [Forms]![fdataentry]![NewInv] & "));"
  30. rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
  31. If rs.BOF And rs.EOF Then
  32.     MsgBox "No header exists"
  33. Else
  34.     MsgBox "a header exists; let's do the following"
  35.     Cancel = True
  36.     'Me.RecordSource = strSQL
  37. End If
  38.  
  39. rs.Close
  40. Set rs = Nothing
  41. Set Conn = Nothing
  42. End
  43.  
  44.  
  45. Err_handler:
  46.     Select Case Err.Number
  47.         Case "3022"
  48.             DoCmd.SetWarnings False
  49.                 MsgBox "A header record already exists for this invoice."
  50.                 Unload Me
  51.                 DoCmd.SetWarnings False
  52.                 Exit Sub
  53.         Case Else
  54.             MsgBox "Error in AllowDataEntry module. Report: " & Err.Number & " " & Err.Description & "."
  55.     End Select
  56.  
  57.  
Mar 28 '07 #6

Rabbit
Expert Mod 10K+
P: 12,427
You can use DoCmd.FindRecord. You can get all the syntax and info you need from the microsoft visual basic editor help files.
Mar 28 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.