Hi,

I'm trying to do the following :

I have a Promtion_Code FORM based on a Promotion_Code table with primary key promotion_code - autonumber.

A CR_DEFECT FORM based on a CR_DEFECT table – primary key- ref_number -text

and a Promotion_items FORM which links both as 1 promotion code can have many CR_DEFECTS. The primary keys in this table are promotion_code and cr_defects



On my Promotion_code form i want the user to be able to enter all details relevant to a promotion.



1. I then want a button which will save all the details of the form and write it to the promotion code table.

2. store the auto number value that Access has assigned in the promtion_code primary key field,

3. open the form promotion_items and populate that stored number in the promotion_code field and then allow the user to select which cr_defects can go with that promotion.

The pop up form will be in datasheet view but I’m open to suggestions if there is a better way.

Been taking me a while to figure this out so any help is greatly appreciated.

Thanks

Parma

Parma,

Is this form bound or unbound (is there a recordsource for the form)?

If it is bound, your button should have a docmd.save block for the Click event. Then in your table, have the CR_Defect field be an indexed field. after the save, preform a recordset to find the AutoNumber value:

dim rs as recordset
set rs=currentdb.openrecordset("CR_Defect")
rs.index = "CR_Defect"
rs.seek "=", txtCR_Defect
txtID=rs!ID

Where txtID is an invisible text box on the form and ID is the AutoNumber field. You do not need to write this number to the form, but sometimes it is easier. This number can then be inderted where you need it.

The dame thing will be done for an unbound form, but you will have to code the AddNew ....Update protion of the recordset.

Hi,

Thanks for your reply. The form I am on, and the form which will be opened to choose which CR_DEFECT's go with each promotion are both bound.

I have used the following code:

Private Sub btn_open_promotion_frm_Click()

DoCmd****nCommand acCmdSaveRecord
DoCmd.OpenForm "PROMOTION_ITEMS" '(the form I want to open, and carry the autonumber value to)

Dim txtCR_DEFECT As String
Dim TEXT_PROMOTION_ID As String

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("CR_DEFECT") (This is a table?)
rs.Index = "CR_DEFECT_REF_NUM" (Index field in the CR_DEFECT table)
rs.Seek "=", txtCR_DEFECT
TEXT_PROMOTION_ID = rs!PROMOTION_ID

End Sub


This code opens up the promotions items table and I am getting the error message item not found in this collection. when i debug, the following code is highlighted :

TEXT_PROMOTION_ID = rs!PROMOTION_ID

Also the autonumber doesn't seem to have been populated. I had two put the two string variables in as access kept saying they were undefined.

Thanks for your help once again.

Parma

Sorry for the delay in resopnding (out of town trip).

Let me see if I can clear some things up.

First,
Set rs = CurrentDb.OpenRecordset("CR_DEFECT") (This is a table?)

The answer to this is yes if that is the table name where your data is. If not, replace it with the correct name.

Second,

Where are you declaring your variables? I have never had good luck declaring them any where but at a global level (the entire project). To see if this will help, create a new module, and add:

Public txtCR_DEFECT As String
Public TEXT_PROMOTION_ID As String

This may cause you a problem since a variable can only be "seen" by another sub or function if it is declared as public or at a high enough level (i.e. at the form level as opposed to a sub routine).

Third,

You must set a variable before you open up a form, or refer to the control (i.e. forms!<form name>... not an effecient way to deal wth things).

Fourth,

Is PROMOTION_ID a field in your table? If not, that may be causing your problems. However, I think if you correct the first 3 steps you will find that this code will run.

Hope this will help.

Folks,

1.

DoCmd.Save saves the OBJECT you specify in the argument list, but not the current record.

To save the current record in a bound form use:

DoCmd****nCommand acCmdSaveRecord

2.

To pass a value from a form to an other form, I strongly recommend using the OpenArgs optional argument of the OpenForm method.
In this argument you can pass any value to an opening form.
To pass a value to an opening form use the following:
'---------------
DoCmd.OpenForm "ChildForm",,,,,,Var
'---------------
where "ChildForm" is the opening form's name, and "Var" is the variable holding the value to be passed. The OpenArgs argument is the 7th parameter of the OpenForm method. To avoid run-time errors, make sure that the appropriate number of commas are present in the method's parameter list.
Then, include a line into the OnOpen event handler of the opening form to retrieve the value passed in the OpenArgs argument.
If you wish to put this value to the txtValue field, for example, type this:
'---------------
txtValue=OpenArgs
'---------------

Generally, using public or global variables in a project can be dangerous, and can make debugging more difficult.
I would recommend to reduce the number of these kind of variables as low as possible.
Furthermore, if an unhandled run-time error occurs, then variables often lose their values, and this can cause additional run-time errors in the application.
Another advantage of using OpenArgs is that When an unhandled run-time error occurs, the OpenArgs property does NOT lose its value. It's kept as long as the form is loaded.

BRegs,

TBÁrpi