Problem with new entry on tabbed forms

[pfm721]

I have a main form with a search by VetSSN. If there isn't a record for the
VetSSN it asks if you want to enter a new record. User says yes. It works
fine on tmain form and the first tab which has a subform with information
that needs to go on different linked table. The second tab has yet another
form with related information that needs to be added. When I go to move to
the second tab I get the following error. "You cannot add or change a record
because a related record is required in table vendor."

My table structure is as follows.

tblConsult
ConsultNumber (text) PK
CreatedDate (date)
ReceivedDate (date)
Reason (text)
VetSSN (text)
VendorID (number)

tblVeteran
VetSSN (text) PK
VetLastName (text)
VetFirstName (text)
VetDOB (date)

tblVendor
VendorID (autonumber) PK
VendorName (text)
VendCity (text)
VendState (text)
VendPhone (text)

tblDoctor
DoctorID (autonumber) PK
DocLast (text)
DocFirst (text)
DocType (text)
DocPhone (text)

tblAppointment
AppointmentID (autonumber) PK
AptType (text)
AptDate (date)
Complete (Y/N)
Notes (text)
ConsultNumber (text)
DoctorID (number)
VendorID (number)

the form has the following setup

Main Form
tblVeteran (all fields)
Tab 1
tblConsult
ConsultNumber (text) PK
CreatedDate (date)
ReceivedDate (date)
Reason (text)
Tab 2
tblVendor
VendorName (text)
VendCity (text)
VendState (text)
VendPhone (text)

My best guess is that I need some way to let tblvendor know that a new entry
is being created.

Thanks in advance.

 

[Jeff Boyce]

The error message you're getting is Access' way of telling you that you have
to have a [Vendor] for the subform to refer to.

If you have a vendor on your main form, you should be able to use a subform
that needs to refer to that vendor. Is this your situation?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[pfm721]

I think I am beginning to see the problem. There was no vendor on the main
form. the idea was that the user could lookup a VetSSN if there was a record
then he could go to the vendor tab (tab2) and see who the vendor is. If it's
going to be a new record then he would go to the vendor tab and enter the
vendor from a drop down box if the vendor is in the system. If the vendor was
not in the system then he would be prompted to enter a new vendor.

The error message you're getting is Access' way of telling you that you have
to have a [Vendor] for the subform to refer to.

If you have a vendor on your main form, you should be able to use a subform
that needs to refer to that vendor. Is this your situation?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a main form with a search by VetSSN. If there isn't a record for the
VetSSN it asks if you want to enter a new record. User says yes. It works
fine on tmain form and the first tab which has a subform with information
that needs to go on different linked table. The second tab has yet another
form with related information that needs to be added. When I go to move to
the second tab I get the following error. "You cannot add or change a
record
because a related record is required in table vendor."

My table structure is as follows.

tblConsult
ConsultNumber (text) PK
CreatedDate (date)
ReceivedDate (date)
Reason (text)
VetSSN (text)
VendorID (number)

tblVeteran
VetSSN (text) PK
VetLastName (text)
VetFirstName (text)
VetDOB (date)

tblVendor
VendorID (autonumber) PK
VendorName (text)
VendCity (text)
VendState (text)
VendPhone (text)

tblDoctor
DoctorID (autonumber) PK
DocLast (text)
DocFirst (text)
DocType (text)
DocPhone (text)

tblAppointment
AppointmentID (autonumber) PK
AptType (text)
AptDate (date)
Complete (Y/N)
Notes (text)
ConsultNumber (text)
DoctorID (number)
VendorID (number)

the form has the following setup

Main Form
tblVeteran (all fields)
Tab 1
tblConsult
ConsultNumber (text) PK
CreatedDate (date)
ReceivedDate (date)
Reason (text)
Tab 2
tblVendor
VendorName (text)
VendCity (text)
VendState (text)
VendPhone (text)

My best guess is that I need some way to let tblvendor know that a new
entry
is being created.

Thanks in advance.