Solved

Referential Integrity, Autofill, and lookup fields

Posted on 2002-05-24
10
332 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

856 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