Updating another table from a form

[vegemite]

I have a field in my form that will update a directory table if the name of the person is not already in the directory.
How can I then update another field relevant to that person in the directory.
So if I am adding Joe Bloggs but I also want to update a yes/no field that Joe Bloggs is a consultant.
Thanks in advance

 

[theDBguy]

Hi. How exactly are you updating the table? You should be able to add more than one information during the update.

 

[Deleted member 147267]

The simple answer would be to make the yes/no field part of your form's recordsource as well ( I can only assume the person field is bound) and update it. If that doesn't help, you'll have to provide a lot more info than what you have revealed so far.

 

[vegemite]

This is similar to something I did beforehand but for a subconsultant (obviously, there is an else and endif etc.)
In Directory there is a yes/no field SubConsultant - I just didnt know where to add that in the code below
Yes person field is bound (it populates firstname and surname in the directory)

If MsgBox("Consultant Name is not on list, would you like to add it?", vbYesNo + vbQuestion, "NoRecord") = vbYes Then
If MsgBox("Do you really want to add a new record?", vbYesNo, "ConfirmAddNewConsultant") = vbYes Then
DoCmd.OpenForm "frmDirectory", acNormal, , , acFormAdd, acDialog, CmbSubconsultant & Chr(165) & NewData

 

[arnelgp]

rather than wondering your thoughts on how to add the record on SAME form,
copy that form and use the New form for the Consultant (ticking automatically the Consultant Yes/No field
when the form opens.

 

[Deleted member 147267]

You could simply run an update sql on the field if it isn't on the form?
IMHO your second prompt is redundant and potentially irritating to a user after a time. Why not just ask both questions in one prompt?
BTW, yes/no fields as this type of data isn't a great idea. Better to use actual type values, especially if there is a chance you could end up with a 3rd value (if the current check is no, it implies that there is at least a 2nd value, even if that is "everything else". See http://allenbrowne.com/NoYesNo.html

 

[Pat Hartman]

So on frmA, you are choosing a person from a combo and if the person is not in the list, you want to add him??? If that is the case, you would use the NotInList event of the control on frmA and you would specify the name of a form to open if adding the person required entering more than a single field. So search on Not In List Event for examples.

My personal feeling is that allowing users to add names on the fly simply results in mis-spelled duplicates. Also, is the user adding the new person going to always have access to all the necessary data to support a directory entry? If he doesn't, all the more reason to not allow it.

 

[vegemite]

You could simply run an update sql on the field if it isn't on the form?
IMHO your second prompt is redundant and potentially irritating to a user after a time. Why not just ask both questions in one prompt?
BTW, yes/no fields as this type of data isn't a great idea. Better to use actual type values, especially if there is a chance you could end up with a 3rd value (if the current check is no, it implies that there is at least a 2nd value, even if that is "everything else". See http://allenbrowne.com/NoYesNo.html

Totally agree on second prompt thanks! Does the update just go in that code?

 

[Pat Hartman]

When you are using Access, use the Access method which in this case is the Not In List Event. The whole point of Access is that it is a RAD tool and has built in stuff to help you. Don't fight with it. Don't roll your own solutions.

 

[Deleted member 147267]

Totally agree on second prompt thanks! Does the update just go in that code?

Is there a combo or not? I saw nothing about a combo before Pat inserted the notion and I wonder if you're just ignoring it because it doesn't apply or is it because you missed the posts? I suspect the latter because I don't see how the not in list event is even relevant to updating a yes/no field.

The simple answer to your last question is Yes. You could get more focused answers by posting relevant code procedures rather than snippets that don't reveal much (please use code tags). That would also likely prevent people from providing non applicable solutions.

 

[Pat Hartman]

I have a field in my form that will update a directory table if the name of the person is not already in the directory.

Sure sounds like a combo.

 

[Deleted member 147267]

How can I then update another field

Maybe so; that's why I specifically asked for clarification. Even if it is, the not in list event is not for updating a different field as I'm sure you know, which is the posted issue

How can I then update another field

 

[Pat Hartman]

The NotInList event gives you the option of opening a form where you can edit multiple fields.

As I said, I don't recommend doing this at all. It simply leads to sloppiness and defeats the whole purpose of using a combo because it encourages people to enter typos.

 

[Isaac]

In many cases my approach to combo's is to totally separate the duty of adding entries to it vs. whatever work the end user is performing by selecting from the list. This encourages separation of duties and for the user group as a whole to take seriously the ramifications of adding an entry to a dropdown, and usually involves a difference in access levels (for example, maybe only a supervisor can add an entry). Usually in my databases there is a "Maintenance" tab which (among other things) allow those with the appropriate access level to use a form to add a drop down item to a table somewhere. My personal experience is utilizing the NotInList event to just pop open a quick add form is very close to just allowing values to be directly added, which as someone mentioned comes close to defeating the strict purpose of clean data on having a combobox in the first place. Thus my comboboxes are often set to LimitToList=Yes and AllowValueListEdits=No, and the NotInList event becomes largely irrelevant. That separation of duties has usually been a good experience for the corporate departments my databases have served. This is not a statement of how it should always be done, just a way of handling dropdowns generally that has worked well for me in many cases.

 

[Pat Hartman]

That "somebody" was me and I said it twice

 

[vegemite]

Thanks brains trust you guys are awesome! Sorry this is a side project so I do have large chunks of time off and then get back to it in bursts (which is a real limitation to learning) I am just coming back now. I will stop the limit to list and add a maintenance section instead. I do like that idea. Thanks again