Acess 2003 - Form will not populate fields from table

[Orson9750]

Access 2003
I created forms frmEARP & frmAppProcess

frmEAPR (parent) pulls 3 fields from tblClientMain
fields: ClientID, LastName, FirstName

frmAppProcess (child) has 4 fields from tblAppProcess
fields: AppID, Date, AppProcess(dropdown), ClientID

frmAppProcess is a subform of frmEAPR

I created a command button on my tblClientMain to open frmEAPR
Problem:
The form/subform has been working well until recently. As of late the fields in the parent form (frmEAPR) are not populating values from the tblClientMain. I can get around this by going back one record then moving back to the current record and the fields will then be populated.

I have looked at various threads and some of the access websites. I do not seem to be able to get a handle on this. This database is located on a LAN server through our agency. I also have multiple users working in this database.

Your advice and help would be most welcomed and appreciated.

 

[someboddy]

just requery...

 

[Orson9750]

Thanks already tried that....didn't work. I will keep plugging away at it.

Thank you for your quick response.

 

[Scooterbug]

Access 2003
I created forms frmEARP & frmAppProcess

frmEAPR (parent) pulls 3 fields from tblClientMain
fields: ClientID, LastName, FirstName

frmAppProcess (child) has 4 fields from tblAppProcess
fields: AppID, Date, AppProcess(dropdown), ClientID

frmAppProcess is a subform of frmEAPR

I created a command button on my tblClientMain to open frmEAPR

I'm a bit confused...you cant put a command button on a table.

Problem:
The form/subform has been working well until recently. As of late the fields in the parent form (frmEAPR) are not populating values from the tblClientMain. I can get around this by going back one record then moving back to the current record and the fields will then be populated.

Are you trying to enter data in the frmAppProcess with a record being displayed in the frmEARP? frmAppProcess is linked to the frmEARP via the clientID, correct?

 

[Orson9750]

Thanks for responding.

Sorry you are correct -- my error the command button is on the frmClientMain and yes to your question

 

[Scooterbug]

Sorry you are correct -- my error the command button is on the frmClientMain

Figured as much...just wanted to make sure

and yes to your question

There in lies your problem. Subforms should be the Many part of a One to Many Relationship. When I read your post, my perception is that there can be many AppProcesses for each Client. So you would open up the Client Form with the Client you want. The subform shows the different App Process that are related to the client.

If you are trying to add a new App Process using the client info from the main form and are doing this via the subform, you wont get the client data because the record you are trying to add doesn't have the client ID associated with it yet.

 

[Orson9750]

Great thanks. I do maintenance on the database. I really appreciate your help. I'll let you know

 

[Orson9750]

I am sending a copy of my relationships. I do have a one-to-many relationship. I know I will figure it out at some point.

Thanks

 

[Scooterbug]

After looking at your relationships, I can see why you are having issues. You state that frmEARP is the parent and frmAppProcess is the child. Based off of the relationship, ClientMain is the Parent and both EARP and AppProcess are the child. The only way EARP and AppProcess relate to each other is via the ClientMain. In all other aspects the appear to not be related to each other.

What you should have is a main form with the information from tblClientMain. You can then have two subforms (either side by side or use tabs) that are based on the table that holds data from EARP and AppProcess. If those two tables need to relate to each other (For example, you cant add a AppProcess without an EARP entry) then you should really store the ID of the one in the other.

Also, you shouldn't use Date as a field name. That is a reserved word and will cause you problems down the road.

 

[Orson9750]

I appreiciate your advise. I will do some modifications and see what happens.

Thanks