Solved

Referential Integrity, Autofill, and lookup fields

Posted on 2002-05-24
10
333 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

735 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