Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    173

    Unanswered: Adding New Record to a Hidden Form

    With One Form Open (SalesOrder) I would like to open another form (SalesOrderShipments) in hidden mode, and add a new record to the hidden form in order to retrieve the UniqueID (ShipmentID - Autonumber Field)

    The problem is below...


    I have a Sales order Form Open

    When adding a shipment, I am opening another form (SalesOrderShipments) and adding a new record to it in order to retrieve the UniqueID or ShipmentID, which is an AutoNumber Field.

    I use the code below to open the form and enter a new record

    once the record is saved, I set the value of it's UniqueID on the Sales Order Form...


    This works fine, however; I would like to open the SalesOrderShipments form in hidden mode, but when I do Access enters a New Record on the Sales Order form that is visible instead of Entering a New Record in the hidden form..




    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmSalesOrderShipments"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.GoToRecord , , acNewRec


    -----------------------------------------------------------

    I tried this, but it still adds a New Record to the Open Form (Sales Order)



    'Open the Sales Order Shipments Form in Hidden Mode
    DoCmd.OpenForm "frmSalesOrderShipments", acNormal, , , , acHidden


    'Call a Procedure on the SalesOrderShipments Form to Add a New Record
    Call Forms.frmSalesOrderShipments.OpenNewRecord

    this is a Public SubProcedure that goes to a new record



    Public Sub OpenNewRecord()

    DoCmd.GoToRecord , , acNewRec

    End Sub



    Also, I use the Sales Order Shipments form to view shipments, so Data Entry is set to No..

    Any Help is Appreciated - AB

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Docmd.GotoRecord applies to the current form.

    Do you really need a hidden form in the background to get your UniqueID?
    Why not just run an append query to add the new record, and then find
    the MAX(UNIQUEID)?
    Inspiration Through Fermentation

  3. #3
    Join Date
    Aug 2004
    Posts
    173

    What If...

    Quote Originally Posted by RedNeckGeek
    Docmd.GotoRecord applies to the current form.

    Do you really need a hidden form in the background to get your UniqueID?
    Why not just run an append query to add the new record, and then find
    the MAX(UNIQUEID)?

    Thanks for the Reply -

    I have done the above in other applications, but I wonder if this can cause a conflict with multiple users?

    Example:

    What I need to do is create a unique shipment id in tblShipments

    Let's Say the New Record ID = 100

    then insert the line items from the sales order into tblShipmentItems
    having the shipment id = 100

    What if a user creates a record at the same time as another and one gets the max, while another gets the previous max?

    Maybe this will not happen, but it is something to think about???

    AB

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    What if you added a USERID field to the SalesOrderShipments table?
    When you add then new record, add some kind of ID number that is related to the current user.

    Then your SQL would look like:

    Select MAX(SHIPMENTID) from SalesOrderShipments WHERE USERID = '" & CURRENTUSERID & "'"
    Inspiration Through Fermentation

  5. #5
    Join Date
    Aug 2004
    Posts
    173

    Thanks - here is what I did

    Quote Originally Posted by RedNeckGeek
    What if you added a USERID field to the SalesOrderShipments table?
    When you add then new record, add some kind of ID number that is related to the current user.

    Then your SQL would look like:

    Select MAX(SHIPMENTID) from SalesOrderShipments WHERE USERID = '" & CURRENTUSERID & "'"

    What I did was inserted a new record, then opened a hidden form that selects the MAX of ShipmentID, then I run the insert statement to insert the sales Order line items using the ID value of the hidden form.

    This seems to work, since the initial insert, and the form immediately opening in hidden view makes sure that the line items are associated with the proper shipment.

    If another user generates a shipment, a new record is inserted and the hidden form selects the max of shipmentid, assuring a new record.

    Thanks again - your solutions are helpful - both now and in the future!

    AB

Posting Permissions

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