Solved

Referential Integrity, Autofill, and lookup fields

Posted on 2002-05-24
10
335 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
Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

 

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

627 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