Questions on data in Form and Tables

[ktchan741]

I have created two tables namely
Table_Drawing and Table_Sign.
The Table_Drawing contains the following field:
No, Rev, Title
The Table_Sign contains the following field:
No, Rev, Drafter, Checker, Approval

I have created a form frmEntry with the Record source [Table_Drawing] and have the following field for entry:
No, Rev, Title and a command button "Sign".

Another form frmSign with the record source [Table_Sign] and have the following field for entry: Drafter, Checker, Approval and a command button "END".

The command button "Sign" OnClick() will run a marco that open a Sign Form for user to fill-in the Drafter, Checker, Approval. The "END" button will close the frmSign and return to frmEntry.

The relationship between Table_drawing and Table_Sign are linked by No, Rev.

I have no problem keying in the field No, Rev, Title. All these data are captured into the Table_Drawing.
When I click on the "Sign" Button and the Sign Form pop-up, I key in the Drafter, Checker, Approval. I exit by clicking on the "END" buttton. However, when I open the Table_Sign, the Drafter, Checker, Approval names are there but the No, Rev are empty (Blank??).

Is there anyway to link the No, Rev of the Table_Drawing and Table_Sign?

Also, whenever I exit from frmSign and click to return to edit mistakes I made in the entry, the entry in the frmSign is always blank(??). Is there anyway to lookup what was already key-in since the two Tables are related by No and Rev.

Appreciate Advise.

 

[MinusM]

I guess you are not at all familiar with VBA coding.
You should have the controls for No and Rev on the Sign form also. Otherwise how will the value be entered in the 2nd table.
You can set property visible = false
In the macro on the line after open form

Select Setvalue
Assign these values for
Item -Forms!SigningForm.
Expression- Forms!drawingForm!No

do the same for Rev

For editing you can do this


Select Setvalue
Item- Forms!SigningForm.filter
Expression- "no=" & Forms!drawingForm!No & " and rev='" & Forms!drawingForm!rev & "'"

Select Setvalue
Item Forms!SigningForm.filteron
Expression true

 

[ktchan741]

Hi MinuM,
I am very new to Microsoft Access and VBA coding.
Thanks, the first part of your instructions work. However, if there are multiple entry to the Sign Form, the NO and Rev only appears for the very first entry I made when I open the Table_Sign to check.

The second part of the editing instruction, I am unable to get it working. It prompted me that I am not allowed to assign a value to the expression.

Can both codes be placed in the same command button "Sign"? My intentions is to allow user to add (if the No and Rev are not available in the Table_Drawing) and edit (if the No and Rev are in the Table_Drawing).

Advise appreciated.

 

[MinusM]

I am on the other side of the world. Sorry for the time lag in answering.
Forget about the filter part. In the signing form Set Record Source as "Select * from Signing table where no = Forms!drawingForm!No and Rev=Forms!drawingform!Rev"

This will do for both adding new and editing

Best of luck