Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Referential Integrity, Autofill, and lookup fields

Posted on 2002-05-24
10
Medium Priority
?
337 Views
Last Modified: 2012-05-04
I have a form that starts with a combo-box with a 3-item value list, then a yes/no listbox. Further down on the form are 2 more yes/no listboxes and a combo-box for a lookup field that gets its values from another table (Purchasing Groups).
If I'm entering a new record and I select an item from the first combo-box, all 3 of the yes/no listboxes are automatically set to "no" (there are no default values specified). The first yes/no listbox is the second field on the form; if referential integrity is enforced between the main table and the Purchasing Groups table, as soon as I try to tab past the first yes/no box, I get an error box that says "You cannot add or change a record because a related record is required in table 'Purchasing Groups'." If I turn off referential integrity, the problem goes away, but the autofill behavior is still the same.
How do I solve this and maintain referential integrity? Does it allow you 2 fields to be set before giving an error? One workaround I found is that if I skip down to the lookup combo-box (it's the 7th field on the form) with the mouse and select a value, I can fill in the rest of the form without the error. But the lookup box needs to stay where it is, so having to skip to it and then back up is awkward.

Ross
0
Comment
Question by:rdiamond
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 7031689
For this type of situations (required entry sequence) I normally use the visible property of the fields.
Just make the yes/no field(s) invisible and un the afterupdate of the combobox add:
me.yesnofield1.visible = true
me.yesnofield2.visible = true
etc.

Get the idea?

Nic;o)
0
 

Author Comment

by:rdiamond
ID: 7031796
Nic;o),
Sounds like a good idea, but do I have to write a procedure?
Using the expression builder I put
=[Safety].Visible=True
in the AfterUpdate property of the combobox (Safety is the name of yesnofield1), and that did not work. Would your code example work in an expression?

Ross
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7031865
Just "open" the code window for the afterupdate.
(first delete the =.. and then press the [..] button behind the field to select the program code.
Ther enter the lines (with appropriate fieldnames) as I proposed above.

Nic;o)
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:rdiamond
ID: 7032141
Nic;o),
Sounds like a good idea, but do I have to write a procedure?
Using the expression builder I put
=[Safety].Visible=True
in the AfterUpdate property of the combobox (Safety is the name of yesnofield1), and that did not work. Would your code example work in an expression?

Ross
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7032257
A bit afraid for code?
Nothing special about that.
Drop the zipped .mdb in my nico5038 mailbox "at" yahoo.com and I'll add it with comment.
Code isn't that hard, especially as e.g. typing me. will show a list of all fields and properties that are available. Can look overwhelming, but that's only a matter of time to get used to.

What is it exactly that you are intending to do, as I can't figure out the question: "Would your code example work in an expression?"

Nic;o)
0
 

Author Comment

by:rdiamond
ID: 7032258
Sorry about the duplicate comment, I had to hit refresh to get back to the question and it just posted again.
Anyway, the invisible/visible trick works fine, but the other symptoms are the same; it still automatically shows "no" once it becomes visible, even without referential integrity turned back on. Once referential integrity is enabled, the same error message pops up.
So, thanks for trying; any other ideas?

Ross
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7032276
I see, can you orop the zipped .mdb in my nico5038 mailbox "at" yahoo.com and I'll try to teach that form a lesson ;-)

I have some thoughts about the cause, but it's easier checking them on the form.

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7226633
Hi Ross,

I did loose track of this one, how far did we get ?

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7458533

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7490042
Per recommendation, points NOT refunded and question closed.

Netminder
EE Admin
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question