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

    Question Unanswered: passing a parametre to the form being opened

    when I click a button on one form in order to open another form where I want to edit the current client on the first form, I would like to pass on the client_id as a parametre to have the client automatically displayed in the second form, not having to look him up manually. Can anybody show me the necessary code for this? I want to use/open the existing clients form that is linked to a query that lists all clients ascending, but just when opening it like this, I want to bypass that query.

  2. #2
    Join Date
    Dec 2003

    try this

    open the frmDemo

    navigate to any customer (this example has 3 customers in it).

    press the demo2 button.

    it will open a new popup form to the same customer as the first form.

    to see how it works, open the VB editor (alt-F11)

    in frmdemo's button click event is this code:

    Option Compare Database
    Option Explicit
    Private Sub cmdOpenDemo2_Method1_Click()

    If Nz(Me.txtCustomerId, 0) > 0 Then DoCmd.OpenForm "frmDemo2", acNormal, , , acFormEdit, acDialog, Me.txtCustomerId

    End Sub

    when you click the button, DoCmd opens frmDemo2 and passes the current customer id in the text box, txtCustomerId to the OpenArgs parameter of the OpenForm method in DoCmd

    note you could also pass the CustomerId field directly but you have to ensure you save the form first or the underlying table will still have old data in the query

    when frmDemo2 opens, this code in the open event of the form fires off:

    Option Compare Database
    Option Explicit
    Private Sub Form_Open(Cancel As Integer)

    If Not IsNull(Me.OpenArgs) Then

    Dim RS As DAO.Recordset
    Set RS = Me.RecordsetClone
    RS.FindFirst "CustomerId = " & Me.OpenArgs
    If Not RS.NoMatch Then
    Me.Bookmark = RS.Bookmark
    End If

    End If

    End Sub

    it creates a temporary recordset based on a copy of the newly open form, frmDemo2, and then uses the FindFirst method to locate that record. if it's found, it resets the record pointer/bookmark to the new customer record.

    again - how you execute this process depends on a lot of things - for example where you pull the first customer id from, whether or not the form is already open, and other considerations (such as what happens if the user moves to a completely different record on the newly opened form).

    Microsoft Access help has some tips and examples. In the VB Editor, just highlight OpenArgs and press F1 and Access will show you additional help on this topic.

    btw kedaniel, this demo also contains your answer to your original question on how to update a parent form from a subform

    when you open frmDemo - there is a text box called customer record.

    try adding a new customer order in the lower window. add an order amount. if that order amount is greater than the customer sales record in the parent form at the top, a message box tells you the customer has a new sales record and the text box with the customer sales record in the parent form gets updated.

    here's the code in the subform that accomplishes this:

    Option Compare Database
    Option Explicit
    Private Sub txtOrderAmount_AfterUpdate()

    If Me.txtOrderAmount > Me.Parent.txtRecordOrder Then

    MsgBox "This customer has a new record order!", vbOKOnly + vbExclamation
    Me.Parent.txtRecordOrder = Me.txtOrderAmount

    End If

    End Sub

    have fun!

    Joe G
    Attached Files Attached Files
    Last edited by JoeG; 01-03-04 at 11:30.

Posting Permissions

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