I need help in figuring out the best way to tackle a DB solution for my company. Unfortunately I am trying to figure this out while running a start-up, so I hope readers can skip past the "why doesn't he just read up on it" or "search this site ... for your answers" I am burnt out and am just not getting over this little hurdle.

The basics of this DB will be as follows:
Accounts
Deapertments attached to Accounts
Clients attached to Departments
Projects attached to Clients

All of the above are seperate tables, one to many. All of the above will get a autonumber PK as an ID.

My simple problem is as follows:
I create the tables, link the PK of the parent to the same field in the child..
I create a form for Accounts, so far so good.
I create a form for departments that is launched via control button from Accounts form
Here is the problem, as the form is not directly a "subform" I cannot get the Department subform to retain and apply the AccountID on record creation, so when I go back to the department form, the departments are not retained with the specific AccountID that came form the original account form that launched it.

Thanks in advance as I am taking this one step at a time.

It sounds to me as if you require your forms to be synchronised.

If so you can find the information you need in Access Help, search under "Synchronise records between two forms"

It will give you a couple of options to continue with, if you want a command button or maybe have your records change automatically.

Hope this is what you are looking for.

Andy

Welcome to Utter Access Forums!

Not need to be embarrassed as every one of us was in your postion at one time or another.... You can use the OpenArgs argument of the OpenForm method to pass the current PK to the form you are opening. The code would look like this:

DoCmd.OpenForm "NameOfFormToOpen", , , , , , Me.NameOfControlWithPK

In the On Open event of the form you are opening:

If Me.NewRecord And Not IsNull(OpenArgs) Then
Me.NameOfForeignKeyControl = OpenArgs
End If

The above assumes that you are opening your form to a new record (you are not opening it to an existing record). For more details on OpenArgs search Access help (while in the code editor) for OpenArgs.

hth,
Jack

Thanks for all the replies.

I am going to try and implement these suggestions into my DB and see what happens.

Thanks again.

You are very welcome and good luck!

Jack