Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

Referential Integrity, Autofill, and lookup fields

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
rdiamond
Asked:
rdiamond
  • 6
  • 3
1 Solution
 
nico5038Commented:
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
 
rdiamondAuthor Commented:
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
 
nico5038Commented:
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
rdiamondAuthor Commented:
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
 
nico5038Commented:
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
 
rdiamondAuthor Commented:
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
 
nico5038Commented:
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
 
nico5038Commented:
Hi Ross,

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

Nic;o)
0
 
nico5038Commented:

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
 
NetminderCommented:
Per recommendation, points NOT refunded and question closed.

Netminder
EE Admin
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now