Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003

    Unhappy Unanswered: VBA code to store a value and populate a form


    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.



  2. #2
    Join Date
    Nov 2002

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

    If it is bound, your button should have a 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" "=", txtCR_Defect

    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.

  3. #3
    Join Date
    Nov 2003

    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.RunCommand acCmdSaveRecord
    DoCmd.OpenForm "PROMOTION_ITEMS" '(the form I want to open, and carry the autonumber value to)

    Dim txtCR_DEFECT 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

    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 :


    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.


  4. #4
    Join Date
    Nov 2002
    Sorry for the delay in resopnding (out of town trip).

    Let me see if I can clear some things up.

    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.


    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).


    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).


    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.

  5. #5
    Join Date
    Dec 2003
    Budapest, HU


    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.RunCommand acCmdSaveRecord


    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:

    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.


    Last edited by TBÁrpi; 12-09-03 at 21:24.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts