TheSwamp
Code Red => VB(A) => Topic started by: Arizona on March 21, 2007, 07:47:09 AM
-
I have an access database that contains a main table in which I have defined a primary key (Job_Num) as an autonumber field (no duplicates). Initially I had split this table into multiple tables due to the quantity of fields (especially text fields) that were required and I used the primary key to establish the relationships. I brought the table (despite my desire to do this) back together due to the fact that someone else will be the maintainer of this database in the future. *Shudder*
The input form I’m using is composed of a primary form with three subforms. (This is due to the large quantities of controls). The field I’m using to link the subform to the master form is this Job_Num field, however what I’m seeing is that the information entered into the subform is trying to be saved back to the table as a separate record using the same job number, which then fails because it would create a duplicate entry. I feel like the answer is probably staring me in the face, so if anyone has a suggestion or answer, I’d really appreciate the help. Also I really need to get this database released today, if possible…it was due yesterday.
-
Have you got anywhere with solving this problem? Just wondering before I spend time having a play.
-
Off the top of my head I can't see anything wrong with what you have. You might try adding an invisible Job_Num field to each form as well as the main form so that the job numbers stay in sync. Access is weird like that at times.
I have an access database that contains a main table in which I have defined a primary key (Job_Num) as an autonumber field (no duplicates). Initially I had split this table into multiple tables due to the quantity of fields (especially text fields) that were required and I used the primary key to establish the relationships. I brought the table (despite my desire to do this) back together due to the fact that someone else will be the maintainer of this database in the future. *Shudder*
The input form I’m using is composed of a primary form with three subforms. (This is due to the large quantities of controls). The field I’m using to link the subform to the master form is this Job_Num field, however what I’m seeing is that the information entered into the subform is trying to be saved back to the table as a separate record using the same job number, which then fails because it would create a duplicate entry. I feel like the answer is probably staring me in the face, so if anyone has a suggestion or answer, I’d really appreciate the help. Also I really need to get this database released today, if possible…it was due yesterday.
-
Thanks for looking at it. :-)
I did end up getting it complete (and issued) by switching to Plan B which was to use a tabbed form and get rid of the subforms altogether. I had to recreate all the controls since Access would not allow me to copy/paste from the existing form into the tabbed pages. :-(
Access has some unique quirks...
-
hey Arizona , sup..
I take it you get this error when creating NEW records? It seems to work fine with the existing records you have inputted (is that even a word), .. hmm. Anyhow, the reason you get the error is because the Main form must be commited (saved) back to the table.
heres what I did to fix it, ..
Insert this event into all 3 of your subforms:
frmContractor_Eval_Section1
frmContractor_Eval_Section3
frmContractor_Eval_Section5
Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."
End If
End Sub
and insert this event into your main form:
frmContractor_Evaluation
Private Sub Form_AfterInsert()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.frmContractor_Eval_Section1.Requery
Me.frmContractor_Eval_Section3.Requery
Me.frmContractor_Eval_Section5.Requery
End Sub
-
Hi Jose,
Thanks!!!!! That's what I was missing.
I felt like I was missing the obvious but I just wasn't seeing it. I appreciate your help it was driving me nuts :lol:
BTW, good to "see" you!
-
Ditto, .. I've been lurking, .. just haven't posted quite as much as I used to over in the Forum WE NO LONGER SPEAK OF.
Glad to be of assistance, its always the little things we end up spending hours on.