Input data in two tables

[Guest]

Assume I hv 2 tables as below :

tblcif tblacct
Cust_ID-------------Cust_ID
Cust_Name Acct_No
Product_Code

Q.

How to input new data into two tables by using one form and without having
to key-in twice Cust_ID number.?

Currently i'm using 2 separate forms to enter new data into the two table?

Appreciate your reply..

 

[Guest]

Hi Zyus,

You'll need to use two forms either way. You can either use a standard form
with subform arrangement that you can get with a wizard-built form, in which
case Access should handle this for you, or you can use a linked form that is
opened from the main form based on tblcif.

To use the linked form idea, which is a bit more complicated, try the
following. FormA in your main form (tblcif data), and FormB (tblacct) is the
linked form:

From FormA, you will open FormB as follows:

DoCmd.OpenForm "FormB", OpenArgs:=Me.[PrimaryKeyFieldName]
_____________________

FormB includes the following code in it's Open event procedure:

Option Compare Database
Option Explicit

Dim lngTest As Long

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError

If Not IsNull(Me.OpenArgs) Then
lngTest = CLng(Me.OpenArgs)
Else
MsgBox "This form should not be opened by itself." _
& vbCrLf & vbCrLf _
& "It should only be opened from FormA", _
vbCritical, "Your Title Goes Here."
Cancel = True
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in Form_Open event procedure..."
Resume ExitProc
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError

If Me.NewRecord Then
Me!Cust_ID = lngTest
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in Form_BeforeUpdate event procedure..."
Resume ExitProc
End Sub



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Tom

Some more info...on linked form idea...Can elaborate further on this line
"DoCmd.OpenForm "FormB", OpenArgs:=Me.[PrimaryKeyFieldName]"

How to execute this line in my main form..is it thru command button?

Thanks

Hi Zyus,

You'll need to use two forms either way. You can either use a standard form
with subform arrangement that you can get with a wizard-built form, in which
case Access should handle this for you, or you can use a linked form that is
opened from the main form based on tblcif.

To use the linked form idea, which is a bit more complicated, try the
following. FormA in your main form (tblcif data), and FormB (tblacct) is the
linked form:

From FormA, you will open FormB as follows:

DoCmd.OpenForm "FormB", OpenArgs:=Me.[PrimaryKeyFieldName]
_____________________

FormB includes the following code in it's Open event procedure:

Option Compare Database
Option Explicit

Dim lngTest As Long

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError

If Not IsNull(Me.OpenArgs) Then
lngTest = CLng(Me.OpenArgs)
Else
MsgBox "This form should not be opened by itself." _
& vbCrLf & vbCrLf _
& "It should only be opened from FormA", _
vbCritical, "Your Title Goes Here."
Cancel = True
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in Form_Open event procedure..."
Resume ExitProc
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError

If Me.NewRecord Then
Me!Cust_ID = lngTest
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in Form_BeforeUpdate event procedure..."
Resume ExitProc
End Sub



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Assume I hv 2 tables as below :

tblcif tblacct
Cust_ID-------------Cust_ID
Cust_Name Acct_No
Product_Code

Q.

How to input new data into two tables by using one form and without having
to key-in twice Cust_ID number.?

Currently i'm using 2 separate forms to enter new data into the two table?

Appreciate your reply..

 

[Guest]

Hi Zyus,

How to execute this line in my main form..is it thru command button?

Yes, that's one way. Use whatever seems most appropriate in your situation.

I forgot to mention that the example that I gave you is for a numeric
primary key/foreign key data type. If your keys are text, then you'll need to
do some modifications. Post back if this is so, and you are not sure of what
to change.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi Tom

Some more info...on linked form idea...Can elaborate further on this line
"DoCmd.OpenForm "FormB", OpenArgs:=Me.[PrimaryKeyFieldName]"

How to execute this line in my main form..is it thru command button?

Thanks

 

[Guest]

My Cust_ID field is a text data type and unique. What are other alternative
beside command button...?

FYI i want to create a form for new data entry for both CIF and to key in
new acct info at the same time?

Currently i create the CIF first then i will input into the acct table....

Thanks

Hi Zyus,

How to execute this line in my main form..is it thru command button?

Yes, that's one way. Use whatever seems most appropriate in your situation.

I forgot to mention that the example that I gave you is for a numeric
primary key/foreign key data type. If your keys are text, then you'll need to
do some modifications. Post back if this is so, and you are not sure of what
to change.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi Tom

Some more info...on linked form idea...Can elaborate further on this line
"DoCmd.OpenForm "FormB", OpenArgs:=Me.[PrimaryKeyFieldName]"

How to execute this line in my main form..is it thru command button?

Thanks

 

[Guest]

What are other alternative beside command button...?

Well, for example, I have a database that has a standard form + subform.
Each subform record can have many comments associated with it (1:M). Rather
than placing a second linked subform on the same form, I have the user
double-click a selected record in the subform to open the comments form as a
continuous form.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Based on your example how to modify if my primary key is text data type....

Thanks