Combo Box won't update... and oh, how I have searched (1 Viewer)

[Colin@Toyota]

Hi all,

I've been searching for a couple hours, and I can't seem to find the answer to my problem on any number of forums. I will start by explaining it...

I have a combo box on an invoicing form that is populated by a select query that lists records of active projects (by project number - a user defined 5 digit number); whether a project is active or not is determined by a checkbox (a yes/no field in the table). Using the OnNotinlist event, I have it set up so that a project editting form is opened where the user can search by project number, and select "Active". The problem arises when going back to the first form, the combo box wont update. Properties for the combobox include that null values are unacceptable, because each invoice that a user inputs must be assigned to an active project.

I have found lots of examples about adding a record through the OnNotinlist event, but changing some data in an existing record seems to have stumped me.

Any help will be greatly appreciated! Thanks

 

[rainman89]

have you tried a

forms!formname.controlname.requery in the onclose event of the editing form?

 

[Colin@Toyota]

Yep.

Also tried me.requery;
tried a bunch of macros;
tried opening the query in datasheet view, then closing it;
tried taking off the required entry property, tabbing to the next control, then going back.

I don't know what else to try...

 

[rainman89]

can you post a sample so we can take a look?

 

[Colin@Toyota]

Here it is:

If you want to try using the database first, go to "Vendor Invoicing", "Add New".

The first field is SETR, this is the project number I was talking about. The combobox is currently populated with records from tblSETRs that have the "Active" checkbox checked. Type in 5J474 or 5J486. These are projects that are in tblSETRs, but are not active. This will open the SETR maitenance form. Type in the SETR, and check "Active". Hmm... You probably could have figured this all out yourself. Oops.

Thanks a lot for taking the time to look at this for me. Let me know if you have any questions, and I will answer them as quickly as I can.

Cheers!

 

[rainman89]

I believe its a conflict between the not inlist option and the requery. ill have to look into it some more. not sure how much time i will have today but ill try

 

[Colin@Toyota]

Any time you spend is truly appreciated. Thank you very much!

 

[Adamr22]

Use Combo Wizard?

I'm a bit new to access but I have a similar situation where my coworkers need to update active aircraft on a form. I did it by using the combo wizard. When you open the combo wizard it will ask you where you would like to get your information from. Tell it to use the query that you have set up for the 'active' projects. Upon clicking the 'Next' button you'll see an option for where you would like to store the information. Select the appropriate destination for your project number. Then go on and finish the wizard. Now whenever a project is clicked 'True' or 'Not true' based on the query, it will show up in your combo box.

Hope this helps. Good luck.

Adam

 

[Colin@Toyota]

I tried that... a couple different ways. I have it working that the project management form opens, and you can set the project as active... Its just that when you get back to the combo box, it wont allow a requery so the newly activated project does not show up. The only way it does is by closing the form, and reopening it. But because this combo box has to be a required entry, this screws up the autonumbering system we use to link the hard copies of the invoices to their place in this database...

Thanks though...

 

[CraigDolphin]

Colin, here's your db with the issue fixed and a couple of tweaks.

Firstly, the notinlist event has an argument 'Response' which is supposed to be defined during the code. If you set Response = acDataErrAdded then the combo box requeries itself automatically after the code is executed. If the Response is set to acDataErrContinue then the combobox does not requery and the user must choose again.

Secondly, you used a macro to open the 'editing' form bound to your tblSETRs table, and forced the user to navigate to the desired SETR ID and edit the active checkbox. What happens if the user really want to create a brand new SETR/project?

Accordingly, I have replaced the macro with some VBA code that:
First checks to see if the SETR Exists.
If the SETR exists, it opens the editing form to that SETR ID.
When the user closes the form, the code checks to see if they made the SETR active.
If so, it sets the response to acDataErrAdded (makes it requery)
If not, it sets the response to acDataErrContinue (makes the user choose a different SETR)
If the SETR does not already exist, it asks the user if they want to create a new SETR...
If yes, then it opens the form to a new record for the user to populate
(After the user populates the form and closes it, the code once again checks to see if this new setr has been marked as active. If not, the user must choose again. If so, the response is set to acDataErrContinue and the combo box requeries to allow the new value.)
If the user doesn't want a new SETR, then the user has to pick again.

Hope it helps.

 

[Colin@Toyota]

That works perfectly! Thank you so much for taking the time to do that. I have a ways to go with my VBA... I am investing in a book or two tomorrow - that way I will be able to understand what the heck you did.

Cheers!

 

[CraigDolphin]

Glad I could help

 

[Colin@Toyota]

Glad I could help

Me too!!! Haha

 

[Colin@Toyota]

Turns out, its not working as perfectly as I thought... I only took a quick look at it yesterday, and everything seemed to be working. As it stands right now, I cannot add a new vendor invoice record, because it says there needs to be a corresponding record in tblSETRs. I don't understand... the relationship between tables is still intact... the bit of code added shouldn't have affected the data storage... would it?

 

[ajetrumpet]

As it stands right now, I cannot add a new vendor invoice record, because it says there needs to be a corresponding record in tblSETRs. I don't understand...

When did you set those relationships up?? Just recently?? You have a composite on your parent table and only one PK is linked to the child. You also have a lookup box in the SETR's table with two columns in it....??

Wondering this too...why do you have a Primary key in the invoice table (the autonumber)?? I know it's for record identification purposes, but is there any other reason??

(I guess everyone is putting their 2 cents worth in for you Colin. )

 

[Colin@Toyota]

The relationship between the two tables has been there from the birth of this db.

Hm. I don't have a good answer for you about the two PKs in tblSETRs... I think I mentioned in that queries thread you healped me out with that I recently came back to this department to find my little access project had been slightly mangled by the last person to work on it... Looking at it now, each SETR is unique in that table, so I guess the Autonumber is redundant there... As for this lookup box... do you mean the one on the SETRs form? Cuz that one weirded out on me - Access added both columns to the lookup even though I only wanted the SETR ID (the actual project number, not the Autonumber)...

The Autonumber, like you said, is for record keeping purposes... We write the corresponding number in the top corner of the hard copies of the invoices, just in case some day we have to go back and check something.

As for the 2 cents... I will take all the change I can get! I just hope someday I will be able to help some people on this forum, so I don't become known as the panhandler of the access-programmers forums!!!

 

[ajetrumpet]

Looking at it now, each SETR is unique in that table, so I guess the Autonumber is redundant there

No, they are not unique in the invoices table; I ran a DISTINCT statement to check it.

As for this lookup box... do you mean the one on the SETRs form?

No, the one that is specified in the table field itself (you can see it in table desing view).

Cuz that one weirded out on me - Access added both columns to the lookup even though I only wanted the SETR ID (the actual project number, not the Autonumber)...

Creating a combo box with the wizard will do this everytime. By default, it is including an identifyer for each value in the field(s) you want. It doesn't matter most of the time, as long as you have that column hidden.

 

[Colin@Toyota]

No, they are not unique in the invoices table; I ran a DISTINCT statement to check it.

Sorry, I thought you were talking about tblSETRs, not tblInvoices...

No, the one that is specified in the table field itself (you can see it in table desing view).

Do you mean the lookup tab at the bottom of the screen? For which field, SETR, Recovery Type, or Active? None of the fields are Lookup data type...
Also, this is another area that I haven't looked into since I've been back in this department... It worked ok when I started using it again, so I didnt think I had a reason to inspect everything. It was only when the code to lookup the active SETRs in the combo box that it started not letting me add any new records.

 

[ajetrumpet]

Colin,

regarding the problem, here may be some hints...

** The Invoices Form is specified as data entry. As soon as you enter a SETR value, weather it be in the list or not, Access recognizes the keystroke action and automatically assigns an autonumber next in sequence that has not already been activated previously, thus reading the current activity as a record entry (in process) to the table.

** The rule = Records can not be entered in a child table before a master record that it can relate to is entered in the Master table.

** Maybe it is not letting you save the record because the initiation of the record entry in the child table (Invoices Form) has already begun....???

Another idea....the code is not functioning properly, it's out of sequence, etc...

 

[Colin@Toyota]

So the referential integrity has been compromised? How would that happen on its own? I tried changing frmInvoices to not be limited to data entry, but that didn't work either...

I understand about the necessity for there to be a master record before there can be a child record, but what I don't get is why this would be a problem if there is already an existing record for that SETR...

Since it was working before the code was added... I would have to assume there is something in there that isn't where it's supposed to be, or isn't doing what it should. Unfortunately, I don't know how to evaluate what is right and wrong there... I can read it and basically understand the logic behind the statements... but I wouldn't know where to start changing things.

Also, thank you for not telling me outright what is wrong... You are forcing me to learn (or try anyways)... So, wise Ant, this young Grasshopper could use another hint or two