Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    KAffleck is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    23

    Macro to open form with data as specified in unbound text bx in another form.

    Hi, I have 2 forms, SearchFM and BasicFm.
    What I would like to do in my SearchFm is type in variable text (in this case a chemical name) in an text box (ChemicalSearchBx), click a button (searchBt) and it to open my BasicFm with all the data related to the chemical name I typed in.
    I have set up my BasicFm and linked it to my tables and I can use the navigation buttons to scroll through my data.
    I tried to set a macro that on click of my searchBt that opened my BasicFm where [chemicalSearchBx]=[Forms]![BasicFm]![ChemicalName]. Chemicalname is the link to that field in my table.
    I get a pop-up box to type in but it just returns a blank BasicFm.

    How do I a) link my ChemicalSearchBx to my macro? So I don't get a pop-up box.
    b) Get my basicFm to return with the data that matches when I've typed into ChemicalSearchBx?



    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Here's the VBA method:

    http://www.baldyweb.com/wherecondition.htm

    the first part needs to be the field name, the second the name of the textbox. Yours look reversed.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    KAffleck is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    23
    Thanks, I'll try that.
    I wondered about it being reversed but access didn't give me the option of doing it the other way round, it just didn't recognise it.

  4. #4
    KAffleck is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    23
    Sorry that didn't work. I'm using the macro builder.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't use macros, but try

    [ChemicalName]=[Forms]![BasicFm]![chemicalSearchBx]

    You might try the VBA method if that doesn't work.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    KAffleck is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    23
    Thanks. I've tried that before and it doesn't like it. How do I use the VBA method? I'm very new to this and working my way through things.
    If I use that it opens my basicFm but it's blank, no matter what I type in my chemicalSearchBx.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It goes here if that's what you mean:

    http://www.baldyweb.com/FirstVBA.htm

    If you're still stuck, can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    KAffleck is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    23
    Hi,

    I did this
    Private Sub ChemNameSearchBt_Click()
    "BasicFm", , , "ChemicalName = '" & Me.chemicalSearchBx & "'"

    End Sub

    and I just get a syntex error.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You forgot

    DoCmd.OpenForm

    at the beginning.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    KAffleck is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    23
    AhHa! thanks I'll try that.

  11. #11
    KAffleck is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    23
    WooHoo! That worked, thanks. What code would I need to add so that it will recognise part words? it only works with complete correct spellings.

    I'm thinking that might not be possible with the form I have set up I would maybe need to do an intermediate form that will give me multiple options that I can then chose one from to give more details?

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can do a wildcard search. Presuming you want "anywhere in the text" the last part becomes:

    "ChemicalName Like '*" & Me.chemicalSearchBx & "*'"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    KAffleck is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    23
    That's fantastic, thank you for all your help. I'm sure I'll be back.

  14. #14
    KAffleck is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    23
    Back again. I want o do the same thing with another search bx and I've copied the same code but changed the names (chemicalName to FormulaTXTBx) but it isn't working. Gives me a parameters pop-up box and then doesn't return the thing I'm looking for. Any suggestions?

    I've done the same with another txtbx and I get a different error....

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The parameter prompt is Access telling you it can't find whatever is named in the prompt. Double check the spelling of both the field and control names, and make sure that field exists in the record source of the form being opened.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 5
    Last Post: 01-07-2019, 01:51 AM
  2. Replies: 3
    Last Post: 07-19-2017, 12:40 PM
  3. Replies: 3
    Last Post: 03-19-2015, 02:11 PM
  4. Replies: 1
    Last Post: 08-20-2013, 02:17 PM
  5. Replies: 4
    Last Post: 04-22-2011, 03:08 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