Author Topic: Access Database Help  (Read 2449 times)

0 Members and 1 Guest are viewing this topic.

Arizona

  • Guest
Access Database Help
« 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.

Tuoni

  • Gator
  • Posts: 3032
  • I do stuff, and things!
Re: Access Database Help
« Reply #1 on: March 21, 2007, 11:59:37 AM »
Have you got anywhere with solving this problem?  Just wondering before I spend time having a play.

Dave R

  • Guest
Re: Access Database Help
« Reply #2 on: March 21, 2007, 01:06:45 PM »
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.


Arizona

  • Guest
Re: Access Database Help
« Reply #3 on: March 21, 2007, 06:31:56 PM »
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...

joseguia

  • Guest
Re: Access Database Help
« Reply #4 on: March 22, 2007, 09:55:33 AM »
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


Code: [Select]
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
Code: [Select]
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


Arizona

  • Guest
Re: Access Database Help
« Reply #5 on: March 22, 2007, 10:47:39 AM »
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!

joseguia

  • Guest
Re: Access Database Help
« Reply #6 on: March 22, 2007, 10:56:01 AM »
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.