Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2008
    Location
    Cardiff
    Posts
    47

    Unanswered: Navigating Access Forms (Was "Please help an MS Access Newbie")

    Hi Everyone,

    Please would it be possible if you could advise me on the following:

    I've just created a MS Access Database containing 3 Forms:

    Customer Index
    Customer Contact Details
    Customer Support Details

    I've created the links and all forms contain the information that I want other users to view.
    Is it possible to create some kind of User Front End which will enable the user to view the database as one file rather than having to open the database and then having to click on the Forms?
    Is it possible to compile all of the forms into a Single 'READ ONLY' file, ie: an .exe file or something.
    Any advice on this would be most appreciated - Many Thanks....

  2. #2
    Join Date
    Nov 2008
    Location
    Cardiff
    Posts
    47

    Linking MS Access Forms

    Hi Everyone,

    I've just started learning how to link Access Forms and seem to be having difficulty in completing this last link, please can you help on the following:

    I have 3 Access forms in my Database:

    Customer Index
    Customer Contact Details
    Customer Support Details

    The Customer Index Form contains an A to Z list of Customers I support. Each Customer as a link to the Customer Contact Details and then this form links to the Customer Support Details. Each Customer as a unique Cust ID which gets filtered through to the same Cust ID on each seperate form, so every Customer uses a different record, as expected...

    I'm able to Link from the Index to Cust Contact Details and then onto the Support Details successfully, all linking to the relevant Customer ID.
    The problem I'm having is when I want to link back from Customer Support Details to the Contactb Details it does not filter correctly, I always seem to go back to Cust ID 1, which in my Index is the first Customer...

    Below is the Code I've used for each form:

    Customer Index Form:

    The Code below are the links per Customer to the Customer Contact Details Form:

    Option Compare Database
    Private Sub Command4_Click()

    Dim stDocName As String
    Dim stCriteria As String

    stDocName = "Customer Contact Details"
    stCriteria = "[Cust ID] = " & Me![Cust ID]
    DoCmd.Close
    DoCmd.OpenForm stDocName, , , stCriteria


    End Sub
    Private Sub Command110_Click()

    Dim stDocName As String

    stDocName = "Customer Index"
    DoCmd.Close
    DoCmd.OpenForm stDocName

    End Sub
    Private Sub Combo6_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Cust ID] = " & Str(Nz(Me![Combo6], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub

    Customer Contact Details Form:

    Code below is the Link to the Customer Support Details:

    Option Compare Database

    Private Sub Cust_Suppoe_Click()

    Dim stDocName As String
    Dim stCriteria As String
    stDocName = "Customer Support Details"
    stCriteria = "[Cust ID] = " & Me![Cust ID]
    DoCmd.Close
    DoCmd.OpenForm stDocName, , , stCriteria

    End Sub
    Private Sub Command110_Click()

    Dim stDocName As String

    stDocName = "Customer Index"
    DoCmd.Close
    DoCmd.OpenForm stDocName

    End Sub


    Private Sub Frame96_Click()

    End Sub


    Customer Support Details Form:

    The Code below is the link back to Customer Contact Details:

    This is link I'm having issues with!!

    Private Sub Link_to_SCI_Contact_Details_Click()

    Dim stDocName As String

    stDocName = "Customer Contact Details"
    DoCmd.Close
    DoCmd.OpenForm stDocName

    End Sub
    Private Sub Command88_Click()
    On Error GoTo Err_Command88_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    Exit_Command88_Click:
    Exit Sub

    Err_Command88_Click:
    MsgBox Err.Description
    Resume Exit_Command88_Click

    End Sub
    Private Sub Command89_Click()
    On Error GoTo Err_Command89_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    Exit_Command89_Click:
    Exit Sub

    Err_Command89_Click:
    MsgBox Err.Description
    Resume Exit_Command89_Click

    End Sub
    Private Sub Command90_Click()
    On Error GoTo Err_Command90_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    Exit_Command90_Click:
    Exit Sub

    Err_Command90_Click:
    MsgBox Err.Description
    Resume Exit_Command90_Click

    End Sub
    Private Sub Command91_Click()
    On Error GoTo Err_Command91_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    Exit_Command91_Click:
    Exit Sub

    Err_Command91_Click:
    MsgBox Err.Description
    Resume Exit_Command91_Click

    End Sub
    Private Sub Command92_Click()
    On Error GoTo Err_Command92_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    Exit_Command92_Click:
    Exit Sub

    Err_Command92_Click:
    MsgBox Err.Description
    Resume Exit_Command92_Click

    End Sub
    Private Sub Command93_Click()
    On Error GoTo Err_Command93_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    Exit_Command93_Click:
    Exit Sub

    Err_Command93_Click:
    MsgBox Err.Description
    Resume Exit_Command93_Click

    End Sub
    Private Sub Command94_Click()
    On Error GoTo Err_Command94_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    Exit_Command94_Click:
    Exit Sub

    Err_Command94_Click:
    MsgBox Err.Description
    Resume Exit_Command94_Click

    End Sub
    Private Sub Command105_Click()
    On Error GoTo Err_Command105_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    Exit_Command105_Click:
    Exit Sub

    Err_Command105_Click:
    MsgBox Err.Description
    Resume Exit_Command105_Click

    End Sub

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've merged the threads - the second is a continuation of the first.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've not read all your code but it sounds like you want to go from the last form back to the first, and the first to look as it did when you left it. In that case, don't open the first form from the last form - just close the other two forms. Opening a form reinitialises it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2008
    Location
    Cardiff
    Posts
    47
    Hi pootle flump,

    Thanks for your quick reply...

    Its not really the closing and opening forms I'm having issue's with. The issue is that say for example, I wanting to access a Customer which is 8 lines down the Index - straight away this Customer would uniquely be identified as Cust ID 8 which then be filtered as Cust ID 8 through the other forms. This enables each Customer to have its own unique record of info.
    The problem is if I'm on the 3rd Form - Customer Support Details in Cust ID 8 and I'm wanting to link back to the 2nd Form - Customer Contact Details also Cust ID 8, it doesn't filter correctly, it seems to go back to Cust ID 1 which would be the incorrect Contact details for this Customer...

    Sorry if this is not clear, I can't think of any other way of submitting this information...

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quick advice - in VBA IDE go tools -> Options -> Require Variable Declaration. Also stick "Option Explicit" right at the top of every code module if it is not there already. Debug-> Compile: fix any errors.

    Ok - you want to open the first form and filter it? Repeat the logic you have earlier on:
    Code:
        stDocName = "Customer Contact Details"
        stCriteria = "[Cust ID] = " & Me![Cust ID]
        DoCmd.Close
        DoCmd.OpenForm stDocName, , , stCriteria
    Just edit this code and substitute it for the code that is not opening the form as you want it to.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2008
    Location
    Cardiff
    Posts
    47
    Hi Pootle Flump,

    Great stuff - just amended the code it works a treat...

    Thanks for very much for your help...

Posting Permissions

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