Results 1 to 2 of 2
  1. #1
    chrisjack001 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010

    Select / DoCmd to open another form

    I have a Sub Form called [Reconciliation Account Budget >=5] in a Master form called [RecAcctNum]. These are some of the fields in that form
    Account BudgetCategory Prefix RequisitionNbr
    These are the conditions:
    If the BudgetCategory = 5, 7, 8 or 9 Open that specific record matching the Account, Prefix & RequisitionNbr in another form called [Req Log]
    If the BudgetCategory = 6 Open that specific record matching the Account, Prefix & RequisitionNbr in another form called [Travel]
    When a user DOUBLE CLICKS a record in that Sub Form [Reconciliation Account Budget >=5] based on the conditions, another form should open up to that specific record matching the Account, Prefix and the Req Number field from that subform [Reconciliation Account Budget >=5]. If the user DOUBLE CLICKS on another record in the sub form [Reconciliation Account Budget >=5], the [Req Log] form or [Travel] Form based on the BudgetCategory conditions should change to the new record selected. This is my code I have currently in the Subform [Reconciliation Account Budget >=5] in the VB code editor in the property field “On Current” under the Event tab.
    Private Sub Form_Current()
    Select Case Me.BudgetCategory
    Case 5, 7, 8, 9
    strForm = "Req Log"
    Case 6
    strForm = "Travel"
    End Select
    DoCmd.OpenForm strForm, , , "Account=" & Me.Account
    End Sub

    What it does right now is open the [Req Log] form upon the master form [RecAcctNum] opening and just stays on one record with the same account number as your records in the Subform [Reconciliation Account Budget >=5] but does not change when you select another record. Please help me meet the above conditions based on my script. I am new at this and may be putting the code in the wrong place or missing some codes. Please Help
    Thank you very much

  2. #2
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Before anything, I would seriously consider renaming your subform.

    A subform called: [Reconciliation Account Budget >=5] is a really bad name for a subform. Preferably something like: ReconciliationAccountBudgetOver5 (notice no spaces or odd characters such as >=. These tend to cause problems, if not now, will somewhere down the line.

    Field names and table names should not have any spaces in them, nor odd characters. Although spaces are ok, not having them will again make your life easier. Odd characters such as >= are not good at all. These can often cause problems (even in brackets.)

    Also for future reference, Field names, table names, form names, etc... should not begin with a number. Reserved words should never be used for any table/field names.

    This will again, make your life much, much easier.

    I'll re-read your post again and see if I can come up with a specific answer to your question. I'm still a little confused.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. 2 multi select list boxes on one search form
    By woodey2002 in forum Forms
    Replies: 2
    Last Post: 11-05-2010, 12:44 PM
  2. Replies: 47
    Last Post: 06-17-2010, 03:04 PM
  3. Replies: 2
    Last Post: 02-26-2010, 08:14 AM
  4. Replies: 7
    Last Post: 11-29-2009, 01:44 PM
  5. Combo-box to select item to edit in Form
    By DHavokD in forum Forms
    Replies: 7
    Last Post: 06-05-2009, 01:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums