Creating entry in second table

  • Thread starter Thread starter Steve Newport
  • Start date Start date
S

Steve Newport

Sorry basic question but I am going around in circles.

I have a simple supplier table with Supplier ID as key. I open a new table
with a form in preparation to add a new record in the second table
(Contacts). Form opens OK and you can add entries but I want to copy across
the Supplier ID so that it maintains the relationship. Tried several ways but
cant manage it.

The VB behind the relevant button that opens the form reads:

Private Sub CMD_AddContact_Click()
On Error GoTo Err_CMD_AddContact_Click

Dim stDocName As String
Dim stSupplierID As String

stDocName = "FRM_ContactsAdd"
stSupplierID = SupplierID

DoCmd.OpenForm stDocName, , , , acFormAdd, , acWindowNormal

Exit_CMD_AddContact_Click:
Exit Sub

Err_CMD_AddContact_Click:
MsgBox Err.Description
Resume Exit_CMD_AddContact_Click

End Sub

Which I have messed around with a bit. How should I be doing this?

Many thanks
 
Hi Steve,
Easyest way to do it is include form2 as subform of form1 and put as link
master field supplierID and as link child field the field where you wanna the
supplierID (obviously defined as long if the supplierID is Autonumber).
Otherwise, if you wanna open form2 as an indipendent form you can pass the
supplierID as openarg and put it in the correct field for example in the
afterupdate event of the form.

HTH Paolo
 
OK. I have two tables - Table one is a table with supplier address details
using an autonumber SupplierID as the key. The second table is one for
contacts. Here I use a text field (ah, should that be a number field?) and
put a relationship between the two tables based on the SupplierID.

What I really wanted was seperate forms and assumed there was a way in which
I could pass the supplierID from the Supplier table to the Contacts table,
but this hasn't worked.

One suggestion I had was to put the following command in to the button on
the Supplier form (uses the openarg) :

DoCmd.OpenForm stDocName, , , , acFormAdd, , acWindowNormal, Me.[SupplierID]
which is what I believe Paolo was suggesting. However, this promted a Compile
error saying "Wrong number of arguments or invalid property assignment".

I had also, as instructed, put the following command in the Open Event of
the second form: Me.txtSupplierID = Me.OpenArgs

Any ideas?
 
Steve

You are describing "how" you want to do something. I still don't understand
the "why".

What would having the SupplierID in the Contacts table allow you to do?

By the way, the only reason to store the SupplierID in the Contacts table is
if there is a relationship between "suppliers" and "contacts". What is that
relationship in your situation?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Steve Newport said:
OK. I have two tables - Table one is a table with supplier address details
using an autonumber SupplierID as the key. The second table is one for
contacts. Here I use a text field (ah, should that be a number field?) and
put a relationship between the two tables based on the SupplierID.

What I really wanted was seperate forms and assumed there was a way in which
I could pass the supplierID from the Supplier table to the Contacts table,
but this hasn't worked.

One suggestion I had was to put the following command in to the button on
the Supplier form (uses the openarg) :

DoCmd.OpenForm stDocName, , , , acFormAdd, , acWindowNormal, Me.[SupplierID]
which is what I believe Paolo was suggesting. However, this promted a Compile
error saying "Wrong number of arguments or invalid property assignment".

I had also, as instructed, put the following command in the Open Event of
the second form: Me.txtSupplierID = Me.OpenArgs

Any ideas?

Jeff Boyce said:
I'm not exactly clear what you are doing ...

"I open a new table... to add a new record in the second table..." (and you
have a "supplier" table).

Please describe the table structure a bit more...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

ways
but
Click to expand...
Click to expand...
 
Sorry. The Contacts file contacts contacts for ALL Suppliers in the Supplier
table. However, when you browse the contacts from the supplier file or (in
this case) try to add a contact to a specific supplier being viewed in the
supplier view form, I want it to show only those records in Contact that are
related to the supplier being displayed. Likewise, when viewing a supplier
from the Supplier Table, when I press the "add a new conact button" I want it
to create a new contact for that supplier only.

To this end I use the SupplierID (key in the supplier details table) as the
link to the contacts details in the contact table. This each entry ion the
contact table will have tghe same SupplierID as the master record in the
Supplier Table.

Hope this explains.

Jeff Boyce said:
Steve

You are describing "how" you want to do something. I still don't understand
the "why".

What would having the SupplierID in the Contacts table allow you to do?

By the way, the only reason to store the SupplierID in the Contacts table is
if there is a relationship between "suppliers" and "contacts". What is that
relationship in your situation?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Steve Newport said:
OK. I have two tables - Table one is a table with supplier address details
using an autonumber SupplierID as the key. The second table is one for
contacts. Here I use a text field (ah, should that be a number field?) and
put a relationship between the two tables based on the SupplierID.

What I really wanted was seperate forms and assumed there was a way in which
I could pass the supplierID from the Supplier table to the Contacts table,
but this hasn't worked.

One suggestion I had was to put the following command in to the button on
the Supplier form (uses the openarg) :

DoCmd.OpenForm stDocName, , , , acFormAdd, , acWindowNormal, Me.[SupplierID]
which is what I believe Paolo was suggesting. However, this promted a Compile
error saying "Wrong number of arguments or invalid property assignment".

I had also, as instructed, put the following command in the Open Event of
the second form: Me.txtSupplierID = Me.OpenArgs

Any ideas?

Jeff Boyce said:
I'm not exactly clear what you are doing ...

"I open a new table... to add a new record in the second table..." (and you
have a "supplier" table).

Please describe the table structure a bit more...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Sorry basic question but I am going around in circles.

I have a simple supplier table with Supplier ID as key. I open a new table
with a form in preparation to add a new record in the second table
(Contacts). Form opens OK and you can add entries but I want to copy
across
the Supplier ID so that it maintains the relationship. Tried several ways
but
cant manage it.

The VB behind the relevant button that opens the form reads:

Private Sub CMD_AddContact_Click()
On Error GoTo Err_CMD_AddContact_Click

Dim stDocName As String
Dim stSupplierID As String

stDocName = "FRM_ContactsAdd"
stSupplierID = SupplierID

DoCmd.OpenForm stDocName, , , , acFormAdd, , acWindowNormal

Exit_CMD_AddContact_Click:
Exit Sub

Err_CMD_AddContact_Click:
MsgBox Err.Description
Resume Exit_CMD_AddContact_Click

End Sub

Which I have messed around with a bit. How should I be doing this?

Many thanks
Click to expand...
Click to expand...
Click to expand...
 
Steve

I'm still hearing "how" you do something.

Why? What will having multiple Contacts per Supplier allow you to do (not
in the database, but in the real world)?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Steve Newport said:
Sorry. The Contacts file contacts contacts for ALL Suppliers in the Supplier
table. However, when you browse the contacts from the supplier file or (in
this case) try to add a contact to a specific supplier being viewed in the
supplier view form, I want it to show only those records in Contact that are
related to the supplier being displayed. Likewise, when viewing a supplier
from the Supplier Table, when I press the "add a new conact button" I want it
to create a new contact for that supplier only.

To this end I use the SupplierID (key in the supplier details table) as the
link to the contacts details in the contact table. This each entry ion the
contact table will have tghe same SupplierID as the master record in the
Supplier Table.

Hope this explains.

Jeff Boyce said:
Steve

You are describing "how" you want to do something. I still don't understand
the "why".

What would having the SupplierID in the Contacts table allow you to do?

By the way, the only reason to store the SupplierID in the Contacts table is
if there is a relationship between "suppliers" and "contacts". What is that
relationship in your situation?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

OK. I have two tables - Table one is a table with supplier address details
using an autonumber SupplierID as the key. The second table is one for
contacts. Here I use a text field (ah, should that be a number field?) and
put a relationship between the two tables based on the SupplierID.

What I really wanted was seperate forms and assumed there was a way in which
I could pass the supplierID from the Supplier table to the Contacts table,
but this hasn't worked.

One suggestion I had was to put the following command in to the button on
the Supplier form (uses the openarg) :

DoCmd.OpenForm stDocName, , , , acFormAdd, , acWindowNormal, Me.[SupplierID]
which is what I believe Paolo was suggesting. However, this promted a Compile
error saying "Wrong number of arguments or invalid property assignment".

I had also, as instructed, put the following command in the Open Event of
the second form: Me.txtSupplierID = Me.OpenArgs

Any ideas?

:

I'm not exactly clear what you are doing ...

"I open a new table... to add a new record in the second table..."
Click to expand...
(and
you
have a "supplier" table).

Please describe the table structure a bit more...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Sorry basic question but I am going around in circles.

I have a simple supplier table with Supplier ID as key. I open a
Click to expand...
new
table
with a form in preparation to add a new record in the second table
(Contacts). Form opens OK and you can add entries but I want to copy
across
the Supplier ID so that it maintains the relationship. Tried
Click to expand...
several
ways
but
cant manage it.

The VB behind the relevant button that opens the form reads:

Private Sub CMD_AddContact_Click()
On Error GoTo Err_CMD_AddContact_Click

Dim stDocName As String
Dim stSupplierID As String

stDocName = "FRM_ContactsAdd"
stSupplierID = SupplierID

DoCmd.OpenForm stDocName, , , , acFormAdd, , acWindowNormal

Exit_CMD_AddContact_Click:
Exit Sub

Err_CMD_AddContact_Click:
MsgBox Err.Description
Resume Exit_CMD_AddContact_Click

End Sub

Which I have messed around with a bit. How should I be doing this?

Many thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top