Results 1 to 8 of 8

Thread: open form

  1. #1
    Join Date
    Mar 2004
    Posts
    79

    Unanswered: open form

    I have a form (frmCustMaster). I have a command button to open form for rates (frmRates). The Rates form uses a different table. The relationship is customer number. When command button is clicked, I want to open rates form in edit mode if rates exist for that customer (works fine) but I need it to open in add mode if rates do not exist. It is opening in add mode but I need the customer number from customer master form to be passed into rates form. I don't know how to do this.

  2. #2
    Join Date
    Feb 2004
    Posts
    137
    Can you attach a copy of your MDB file to check out and see where we can be of help? No offense, but I am finding more and more just how explicit people aren't about what they're trying to accomplish.

    "If you only tell the Doctor part of the problem, you can expect to receive only part of the cure." - Anon.

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    Quote Originally Posted by brmk
    I have a form (frmCustMaster). I have a command button to open form for rates (frmRates). The Rates form uses a different table. The relationship is customer number. When command button is clicked, I want to open rates form in edit mode if rates exist for that customer (works fine) but I need it to open in add mode if rates do not exist. It is opening in add mode but I need the customer number from customer master form to be passed into rates form. I don't know how to do this.

    so you need to check if the rates/customer exist first

    why not just count the number of records

    Dim CountRate as long

    CountRate = Dcount("*","RateTable","CustomerID=" & me.CustomerID)

    if CountRate = 0 then
    open form in add mode
    else
    open form in editmode
    end if
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Mar 2004
    Posts
    79
    My problem is not opening in the form, it's passing the data. The customer master record exists and therefore has a customer number. When I open customer rates form in add mode, how do I recall what the customer number from the customer master form is.

  5. #5
    Join Date
    Feb 2004
    Posts
    137
    In the Code Module for frmRates, paste the following code:
    Code:
    Private Sub Form_BeforeInsert(Cancel As Integer)
        Me.CustomerNumber = Forms("frmCustMaster").CustomerNumber
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
        Me.Filter = "[CustomerNumber]='" & Forms("frmCustMaster").CustomerNumber & "'"
        Me.FilterOn = True
    End Sub
    Note that the above code assumes that the common field between the two forms is named CustomerNumber, and that the field in both cases is a text field. Please make the appropriate changes if such is not the case.

    Try it and see if it works for you.

  6. #6
    Join Date
    Mar 2004
    Posts
    79
    It's not working. If the rates exist, the form opens fine and displays the correct related rate information. It is when the rates do not exist that i have the problem. I want the form to open in add mode, with the customer number text field populated with the customer number from the calling form.

    In the above code, it does not even hit the Before Insert Code. I tried to move the code
    Me.CustNumber = Forms("frmCustMaster").CustNumber
    The correct value is in frmCustMaster.CustNumber but
    I get run time error (You can't assign a value to this object).

    I know I can have rates form be bound to custmaster and have subform with custrates info but I figured there has to be a better way.

  7. #7
    Join Date
    Feb 2004
    Posts
    137
    Try pasting this code instead:
    Code:
    Private Sub Form_BeforeInsert(Cancel As Integer)
        Me.CustNumber = Forms("frmCustMaster").CustNumber
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
        Me.Filter = "[CustNumber]='" & Forms("frmCustMaster").CustNumber & "'"
        Me.FilterOn = True
        Me.CustNumber.DefaultValue = "='" & Forms("frmCustMaster").CustNumber & "'"
    End Sub

  8. #8
    Join Date
    Mar 2004
    Posts
    79
    You DID IT!!! Thank you so much.

Posting Permissions

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