We help IT Professionals succeed at work.


brantek asked
Medium Priority
Last Modified: 2008-02-26
One of the forms I created in Access needs to do the following:

I click on the button
this opens a box
I enter the po #
this brings up the form
the form is populated with the records matching that po #

Yes, all my tables, queries, macros and other forms are functioning perfectly.
this one form "debit memo form" only populates with the first record in the query
so I enter po# 14845 which is debit memo #8124
instead it brings up po# 59624 debit memo # 8121

When the form opens, and shows the 8121, if I hit the scroll button on the mouse, it will show all of the records in sequential order.  

How do I get this to populate with that specific record?  When I created this I made it open the query as well as the form so that I could see if it was bringing up the correct record from the query.  It is....  the the query seems to be fine.  I've tried so many variations on the form.  

So even though the form seems to be the culprit, could it actually be the formula in the query?

I hope I explained this clearly enough.  

Watch Question

Top Expert 2016

use the input to filter the records of the form

docmd.openForm "formname", , "[po#]=<po # input>"


Ok, where is ''input"
walkthrough please...:o)
Top Expert 2016

explain this in detail

I click on the button   ' what is the name of the form where this button is. post the codes in the click event of this button

this opens a box        ' what kind of box {input box?}

I enter the po #  '<<<< this is the input will be coming from


Ok here it goes----

I created a button in a different form.  This button will be merged into another database later on.  This form is attached to a macro which opens the the query in form view.  

in my query I specified a search option.  It is a box that pops up and you enter the po# into it.

You hit enter and it brings up the debit memo form.  

at this point the form should be populated with the information coming from the query.  Well it does, but it only shows the first record of four records.  so no matter what po # I enter, it only brings up the first record in the query.

Maybe something like this:

Public Sub YOURBUTTON_Click()

Dim YOURVARIABLE As String 'Where you call out a variable for use within this code.

YOURVARIABLE = inputbox("YOUR MESSAGE HERE",  "INPUTBOX NAME"  'Inputbox opens, user enters information and the variable is set to the user defined information.

docmd.openform ("NEWFORMNAME") 'Opens the form you wish to apply the filter to.

Forms!NEWFORMNAME.Filter = "FILTERFIELDONNEWFORM=" & chr(34) & YOURVARIABLE & chr(34)  'Enters the value from the user into the Filter field of the newly opened form.
      'A variant of this string could also be: Forms!NEWFORMNAME.Filter = "YOURFILTERFIELD=" & YOURVARIABLE

Forms!NEWFORMNAME.filteron = true 'Turns on the filter in the newly opened form.

End Sub

Hope that does the trick! :0)

Btw, you should need to edit only the things that are in ALL CAPS.


Oh my gosh----LOL
I haven't learned to work in the code part of this yet.  I will gladly follow your instructions.  You just need to point out where to start first.  When I saw the words your button I  went into the code view for the area around the button on the form. Because when I click to do code view for the button itself, it does not have that option.

So I am now in code view for the form that the button is on.  
I was going to do a print screen shot for you but this won't allow it.  

Anyway You know what a design view looks like for the form.  I created a button on that.  This is a test button to make sure everything is working off that.  

so I clicked on the form surrounding the button in design view.  this gave me the option for code view.
In code view I see the following:

Option Compare Database

Private Sub Detail_Click()

End Sub

Private Sub DM_History_Button_Click()
On Error GoTo Err_DM_History_Button_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Tia's Form B   "Viewing" for DM History"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit Sub

    MsgBox Err.Description
    Resume Exit_DM_History_Button_Click
End Sub

Is this where I am suppose to start?

Top Expert 2016

<<< but it only shows the first record of four records.  so no matter what po # I enter, it only brings up the first record in the query.>>>

this is because, there are several records return by your filter ( there are more than one record under the po # that you entered), and displaying the first record in the form is a normal behaviour.

if you want to eyeball in a particular record you should add more parameter in your filter so that only the unique record  will be returned.


No there is only one record per po#.  the four records that come up when I scroll are all under different po#'s.  So in other words it is allowing me to view the entire table this is connected to.

Top Expert 2016

is the record source of your form the table? if it is replace it with the query that calls for the input.


That does not work.  When you switch them it will not bring up the form itself and it winds up bringing in the pop up box that you normally would enter the po number into in place of the form.

Top Expert 2016
try entering the po number when the box appears and hit ok. what happens?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


Ok, I went in changed the source from table to query.  Then It brought up the query and the pop up box.  So I went into the macro and deleted the query setting.  Went back to form view with the macro, clicked the button---entered the po# and it populated with the correct po information.  

Thank you for all your assistance Capricorn 1.  You pointed me in the right direction.  This has frustrated me for the last two weeks.  Thanks again.  :o)

Heh, just wonderin...was that code of any use whatsoever??
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.