Updates to Linked Form Not Showing in Primary Form

[lbnear]

When my users are entering data for a project, if they do not see the project in the combo box list on the main form, they can click on a command button that will take them to another form where they can add the project. On the second form, there is a "save" command button and and "return to original form" command button. I have added DoCmd.Save to the "on click" on both the "save" and "return" command buttons.

The problem is that the first time the user adds a new project and returns to the main form, the new project is in the combo box list (or, if multipl users are in the database, whoever adds the first project can see it in the combo box list). The second time the user adds a new project and returns to the main form, the project is not in the combo box list (if the project form is opened, it is in that list). If the user closes the main form and opens it again, the new project is in the combo drop down list. While this is a solution, it's not very efficient!

Any help will be greatly appreciated!

 

[rainman89]

do a forms!yourformname.yourcomboboxname.requery in the close button of the add project form

 

[boblarson]

do a forms!yourformname.yourcomboboxname.requery in the close button of the add project form

If the updates aren't showing up for the other users, you could set the requery in the combo's click event so that it requeries just before the user uses it and therefore would have the latest stuff for everyone whenever they went to use it.

 

[lbnear]

Thanks Bob and rainmain,

After testing rainman's suggestion, and not being able to get it to work, I went to "on click" on the main form (surprisingly to me, I did this on my own - so Bob, thanks for the validation.). However, I can't get my syntax to work there!

Since now I'm not pointing to a control source, I can't use the forms! code, when I tried the DoCmd.requery ([form name]) I get a run time message (error 2465 - can't find the field referred to) when I click into the combo box.

I'm sure it's simple - what am I doing wrong?

 

[boblarson]

You don't want the on click for the form, you want the On Click event for the combo box.

 

[rainman89]

me.requery would work in the onclik of the combo box

 

[lbnear]

Sorry about not being clear, I was testing the requery on the "on click" of the combo box on the main form.

rainman, I tried the me.requery in the combo box and still am only getting the first update from the "add project" form. The subsequent updates are not being reflected in the combo box of the main form.

Any other thoughts?

BTW - thanks for your help!

 

[rainman89]

can you post a sample?

 

[lbnear]

Ray,

If you open "Alison Solano - Input Form" you'll see the "Add Course/Program" button next to the "Course/Program/Project" combo box. When I add a new program and go back to Alison's form, it's there; however, if I do it a second time the new program is not in the combo box.

Thanks again for your help! Lisa

P.S. I know my naming conventions are not proper, but this database will be turned over to my users so I wanted to make everything clear for them.

 

[rainman89]

in the on enter event of the combo box put me.refresh

 

[boblarson]

Okay, a couple of things while I sort everything out.

1. The syntax is Me.YourComboNameHere.Requery, not Me.Requery (Me.Requery requeries the FORM, not the combo box)
2. DoCmd.Save is not for saving records, it saves design changes to the item selected, so in your save button if you want the record saved (and it automatically will anyway when the form is closed) you would use DoCmd.RunCommand acCmdRecordsSave


I'm still looking to see what else might be affecting things.

 

[boblarson]

in the on enter event of the combo box put me.refresh

Ray:

refresh will only affects records that existed in the original query - it will show changes made to EXISTING records, but not added records. Requery is the only one that will add new records to a recordset.

 

[boblarson]

This:

DoCmd.GoToRecord , , acNewRec
DoCmd.GoToControl "Course/Program/Project Name"
DoCmd.Save


can be replaced with:

DoCmd.RunCommand acCmdRecordsSave
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.GoToControl "Course/Program/Project Name"

 

[rainman89]

Ray:

refresh will only affects records that existed in the original query - it will show changes made to EXISTING records, but not added records. Requery is the only one that will add new records to a recordset.

yeah i did not test it with new records. just existing ones. my bad

 

[boblarson]

Ray's suggestion about the On Enter event of the combo though is good. Put it there:

Me.Course_Program_Project_Name.Requery

 

[boblarson]

Also, I you really shouldn't have more than one form for this. Unless there are major differences, and I didn't see any, you should be able to use the same for everyone regardless of who they are.

 

[boblarson]

And another suggestion - get rid of special characters in your object and field names (no using / ' : etc.) as they will only cause you grief at some point. / is a mathematical operator and ' is used as a string identifier when enclosed with double quotes.

I know it would be a fair amount of work to do, given what you've done so far, but I think you're going to run into problems soon if you haven't already.

 

[lbnear]

Ray and Bob,

THANK YOU!!!! The final solution of putting it in the "on enter" works like a charm. I tested multiple project adds and on multiple user forms and it works.

Lisa

 

[boblarson]

Sorry, yet another discovery that will help you - and maybe you won't get it into this one, but maybe for the future.

For your lookup tables, you should have ID fields for each of them and store the ID instead of the text. If the text happens to change at some point, then you would only need to change it in ONE place, instead of hundreds of records. This is part of normalization.

Also, having a table for each person is NOT normalized. You do NOT want repeating objects. In other words you do not want a bunch of tables with the same fields. If they are the same fields then they get stored in the one table. You also don't want repeating fields. So, if you have fields set up like Class1, Class2, Class3, etc. then those should be broken out to another table as you then have a one-to-many relationship and a table is needed to handle that. It will then make your reporting much easier to get what you want out of it.

I hope it doesn't sound like I'm getting down on you for all of this. These are things me, and others, on this site have sometimes learned the hard way and we're trying to pass along the learning.

 

[lbnear]

Bob,

The reason I have a form (and corresponding table) for each person (maybe it was a lack of Access understanding on my part) is that I need them to each enter their hours without being able to:

a) open someone else's form (I still have security work on the database to do)
b) I don't want them stepping on someone else's input
c) The users are already confused by which record number they're on - so if I put a filter on the form they would be really confused with the record numbering (I didn't try an employee name filter on the form, but assumed the numbering would jump all over the place - I should have tested it before I made the assumption).

If you can think of a good way to accomplish this, let me know, 'cause it's a pita to update each of the tables, queries, forms, reports each time I make a change. Also, as I said earlier, the users are going to take over the database, so if I can simplify it now, I should.

Thanks again,

Lisa

P.S. I haven't had any problems (yet) with the /, but I will go back and change it.-