Access Forms 1

[meddling]

I am trying to create a fairly simple database and I'm having such a hard time.
I have 3 tables that I will use as forms. I wanted to display the main table including 2 buttons that would open the other 2 forms.
On Table 1 I have a Employee field, set to Autonumber, Indexed (no duplicates).
One Table 2 (which would correspond to the first button) I have more information I want filed UNDER the Employee in question. And ono Table 3, there is more information that I also wanted filed under the Employee in question.
Basicly, when the main form is opened, you fill out the Employee information, Table 1 would be the banking information and Table 2 would be some general information.
I cannot make any relationships work. Everytime I clicked on the buttons I wanted each employees information to be displayed. I either get nothing or the same information over and over.
Hope this makes sense. PLEASE HELP ME ASAP!!!!

 

[M8KWR]

have you thought about using subforms, its slightly easier.

What version of access are you using.

If you have created the other 2 forms, then if you add a subform, the wizard should be able to help you set it all up, thats if your table designs are what they should be.

You do have an employee field in the other 2 tables, set as Number.

If you need any more help please let me know.

 

[meddling]

Hey there!

I thought about using subforms, but I didn't want all the information ALL in one page. I wanted part of it displayed when you open the database and 2 little buttons corresponding to the 2 other forms.

I do have en employee field in the other 2 tables, set to number, not indexed.

Basicly, when the main form is opened, I fill out the Employee information, click on button 1 and fill banking information and Table 2 would be some other general information. Then once you were to enter a new employee record, the banking and general information would be blank for that employee.

Can you see what i'm trying to do? Hope you can help me out and Thanks for everything so far.

 

[M8KWR]

Ok i think i got want you are trying to do.

In your buttons that you use to open up the other forms you would need to change the code, so that when you press the button it would open up the records information that the main form is on.

I hope thats correct.

Here goes, us this code, but you would have to change FORM_NAME to the form you are trying to open, and EmployeeID to match the ID you are using in your tables, I hope that they are the same, it does make life easier..

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "FORM_NAME"
    
    stLinkCriteria = "[EmployeeID]=" & Me![EmployeeID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Let me know how you get on.

 

[meddling]

I have 3 tables that I will use as forms. I wanted to display the main table including 2 buttons that would open the other 2 forms.
On Table 1 I have a EmployeeID field, set to Autonumber, Indexed (no duplicates).
One Table 2 (which would correspond to the first button) I have more information I want filed UNDER the EmployeeID in question. And on Table 3, there is more information that I also wanted filed under the EmployeeID in question.
Both table 2 and table 3 have an EmployeeID field as well, set to Number, not indexed.
So, when you press the button it would open up the records information that the main form is on.

Someone from here (M8KWR) told me to add this line (below) to the code. It does open up the forms when I click the button, but it does not save the information

stLinkCriteria = "[EmployeeID]=" & Me![EmployeeID]

I have a One-to-One relationship set-up between the main Employee table and the other 2. Not sure how to set up the "Join Type" or to check the Referential Integrity box.

SOMEONE PLEASE HELP...

 

[M8KWR]

you could use following.

In your forms that contains the information about table2, in the employeeID field, edit the default value of this field to

[forms]![TABLE1 FORM NAME]![EMPLOYEEID_FIELD NAME]

I have tested this and it all works for me, i am using A97 though...

If you use a subform then it would automatically update this fields with the employeeID value from table1..

Let me know how you get on

 

[meddling]

I did everything just the way you said it and it didnt work.

I get this message that says "You cannot add or change a record because a related record is required in table 'Employee'.

You haven't mentioned anything as far as how I set up the relationship betweens those tables. Could you clarify about that, if i need to change anything.

Again, I am using Office Professional 2003.

Thank you for all the assistant so far...

 

[M8KWR]

I must admit i do not use the relationship window, i prefer to do it myself.

If you removed the link between the tables it would work.

 

[meddling]

I removed it. Still doesn't work. I can enter information but it does not save the information.

I have left the line you told me to add to the code. Is that correct?

 

[M8KWR]

I have tested it and it does work, I do not know if there is an issue because i am using A97, and you are using A2003.

So you have used the code to open the form that i have provided, and you have updated the default value of employeeID field in the opened form, to the value that i said.

I am unsure what going on due to i'm not seeing what you are seeing.

Just check your spelling of your form names etc...

 

[meddling]

was the last expression supposed to look something like this?

=[Forms]![Direct Deposit]![EmployeeID_EmployeeID]
or
=[Forms]![Direct Deposit]![EmployeeID]


here's how i wrote the first one i added to the code:

stLinkCriteria = "[EmployeeID]=" & Me![EmployeeID]

I beg that you can help me work this out... thanks again!!!

 

[M8KWR]

the first one

[Forms]![Direct Deposit]![EmployeeID]

I did not use a equal sign in my testing, but again i'm unsure of A2003.

The second one look fine. To how it should look. As long as the ID numbers which you have used (EmployeeID) as what you have in each form.

It should be working. I would like to post you the small example that i have create, and see that working, to see where you are going wrong, but from what you have said it should be working but it isn't.

Let me know how you get on.

 

[meddling]

Yeah... I'm gonna double check everything again...

I'll get back with you...

Thanks for all so far...

 

[meddling]

I figured it out...

The line below was supposed to say the Main Form as opposed to the button form


NOT: [Forms]![Direct Deposit]![EmployeeID]

YES: [Forms]![Employeefrm]![EmployeeID]

THANK YOU SO MUCH FOR YOUR ASSISTANCE!!! IT WAS INDISPENSIBLE!!!