Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110

    Cant Get my Buttons to Open a Related Form


    Views: 24 Size: 998.9 KB">Database3.zip
    Database3.zip

    So I have the database in development. I have to Form in Development Form2, which has buttons.

    I want to click on the button and open a Filtered Form with a related table.

    Bt the command ="[CustomerDatabaseID]=" & [CustomerDatabaseID] doesn't work as a macro and the DoCmd version inVBA just brings up a blank form.

    The strange thing is that I have used this code several times throughout the queries forms, but this time I'm trying to open a fome that is essentially a child form, but it is b;ank. I have played with the Invitation Letter button as the one I'm playing with and its giving me grief.

    Anyy help or suggestions would be appreciated.

    Regards

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I see an Intro Letter button, not Invitation Letter.

    Right now there is no data in CustomerDatabaseID field of tblCompanyDDIdentity. Therefore form is blank.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110
    Thats correct . I forgot I changed the letter button name before Zip.

    But when testing it as though it were a fresh database the thing I get is that the database does not join the two tables.

    tblCustomerDatabase.CustomerDatabseID is a one to many join with tblCompanyDDIdentity.CustomerDatabaseID

    So when I have entered data into tblCustomerDatabase via the Lead form, I then click a "Identity" command button. This takes me to the the form Form2, when I click any on the command buttons, even though Form2 has a control source for tblCustomerDatabase in the header, the buttons don't want to open a New record that saves to the join required.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    All your code does is apply filter criteria and since there is no data in field to filter on, form is blank. It is not even coded to open to new record row.

    If you want to create a record in tblCompanyDDIdentity that has CustomerDatabaseID as foreign key to link master and dependent records, either use a form/subform arrangement or code will have to pass CustomerDatabaseID value to form and populate field.

    This is a common topic. Explore OpenArgs property of OpenForm command as one method of passing data between forms. https://www.access-programmers.co.uk...n-help.312716/

    DoCmd.OpenForm "CompanyLetter", , , , acFormAdd, , Me!CustomerDatabaseID

    Then code in form open event:
    If Me.NewRecord Then Me!CustomerID = Me.OpenArgs


    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Steven

    Before you move onto creating Forms you will need to look at your table structures again.

    You are using Lookup Fields at table level. Check Google for the Evils of Lookup Fields in tables.

    You need to remove all spaces in FieldNames.

    You need to recreate your tblCompanyDDIdentity.

    All of the following Fields should be deleted and they would be recorded in a table with the following Fields:-



    tblCompanyRegistrations
    -CompanyRegistrationID - PK Autonumber
    -CompanyID - FK (linked to PK in tblCustomers]
    -IdentityTypeID - FK (Linked to list of Identity Types)
    -DateReceived
    -DateVerifiedByID
    -DateVerified
    -Notes

    tbluIdentityTypes
    -IdentityTypeID - PK - Autonumber
    -IdentityType (Registration,VAT,Address,Bank Address,Trading Address,Nr of Directors)
    Attached Thumbnails Attached Thumbnails Registration.JPG  
    Last edited by mike60smart; 07-12-2020 at 01:06 PM.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  6. #6
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110
    Thanks for your feedback .

    I'm working on these now.

    Regards

  7. #7
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110
    Thanks for this.

    I have taken your advice and have gone through to develop:
    Private Sub - CustLtrBtn_Click()
    IF IsNull = (DLookup("CustomerDatabaseID","tblCompanyDDIdentit y","CustomerDatabaseID ="CustomerDatabaseID") Then
    DoCmd.OpenForm "CompanyLetter", , , , acFormAdd, , Me!CustomerDatabaseID
    Else
    End Sub
    DoCmd.OpenForm "CompanyLetter", , , , acFormEdit, , Me!CustomerDatabaseID
    End If

    However when clicked the code pauses at the line highlighted above and doesn't open the form or give an error but brings up the VB box with the code highlighted above in yellow.
    Should that line be DoCmd.OpenForm "CompanyLetter",,,"[CustomerDatabaseID]=" & [CustomerDatabaseID]

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Steven

    Can you upload your current version?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  9. #9
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110
    Database3 (2).zip

    This is making my head spin.

    I have tried several things in the open event on the form CustomerLetter

    I have tried the code above and reverted to whats in the button on click event for Form2, CompanyLetter.
    A Subform is not something my boss wants, he wants to have buttons because I can make them vanish depending on criteria entered, so thats a cool option.

    So I can open the form in add mode, or open the form in edit mode, but this database is going to go live fresh, so when adding a new lot of data the first click is going to potentially add a new record, and at all other times, edit the data.

    Over time the user may not know what has data and what does not in the form they are clicking into, so having a Add data or Edit Data button is confusing and just one button is required.

    I'm reading up on coding, but I'm just working my way through simple stuff and not passing data from one form and cant find anywhere on the net to get the result to check the opening form for new then add a new record in add mode, if not a new record, open it in edit mode.

    Help.

    As for my table lookup fields I'll finish that when I have time.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Did you mean "CompanyLetter" instead of "CustomerLetter" form?

    Okay, field name is CustomerDatabaseID in tblCustomerDDIdentity, not CustomerID.

    The Me.NewRecord test is not working in Open event, use Load event instead.

    Remove the Requery.
    Code:
    Private Sub Form_Load()
    If Me.NewRecord Then
        Me.CustomerDatabaseID = Me.OpenArgs
        RunCommand acCmdSaveRecord
    End If
    End Sub
    
    Make sure CompanyLetter form is not saved with criteria in Filter property and set FilterOnLoad to No.




    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Be aware that "Number" (tblCountries) is a reserved word in Access and shouldn't be used for object names.

    EVERY module should have these two lines at the top:
    Code:
    Option Compare Database
    Option Explicit

  12. #12
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110
    Thanks June

    You are amazing at this stuff!

    Your suggestion worked.

    I think I had spent so long looking at lots of little things changing tables and names etc that I became blind to the small details and started making typing errors.

    I really appreciate your support.

  13. #13
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110
    Thanks Ssanfu

    I've updated accordingly

    Thank you

  14. #14
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110
    Database3 (3).zipHi so this is still a development work

    Private Sub Letterbtn_Click()
    DoCmd.OpenForm "CompanyLetter", acNormal, , , acFormEdit, , Me!CustomerDatabaseID
    End Sub is good, it picks up the Me!CustomerDatabaseID and the

    Option Compare Database
    Option Explicit
    Private Sub Form_Load()
    If Me.NewRecord Then Me!CustomerDatabaseID = Me.OpenArgs
    RunCommand acCmdSaveRecord
    End Sub

    Does pick up the CustomerDatabaseID for my first record.

    However, when I Open Form Main Menu, Click and Open All Customers, I get CustomerDatabaseID (10), them I click 2.1 Identity, That gives me Form2 with CustomerDatabaseID (10) - perfect, just what I want, but when I click Intro Letter it opens and gives me CustomerDatabaseId=9

    Now as there are no records yet in CustomerDatabaseID=10 for CompanyLetter the
    DoCmd.OpenForm "CompanyLetter", acNormal, , , acFormEdit, , Me!CustomerDatabaseID
    needs to be
    DoCmd.OpenForm "CompanyLetter", acNormal, , , acFormAdd, , Me!CustomerDatabaseID

    What code do I need? I'm assuming an If statement, that looks at the record to see if there is something there, but cant find an example online for my particular issue.

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    As already advised: Make sure CompanyLetter form is not saved with criteria in Filter property and set FilterOnLoad to No.
    Code:
    If IsNull(DLookup("CustomerDatabaseID", "tblCompanyDDIdentity", "CustomerDatabaseID=" & Me.CustomerDatabaseID)) Then
    Life would be so much easier if you used form/subform arrangement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 10-11-2017, 03:13 PM
  2. Replies: 4
    Last Post: 02-12-2015, 11:42 AM
  3. Replies: 2
    Last Post: 06-20-2014, 12:33 PM
  4. Replies: 11
    Last Post: 04-05-2013, 11:35 AM
  5. Command buttons to Open Forms
    By Ace Pioneer in forum Access
    Replies: 1
    Last Post: 11-30-2010, 06:32 AM

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