my

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 #

Problem:
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.  

brantek
brantekAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
use the input to filter the records of the form

docmd.openForm "formname", , "[po#]=<po # input>"
brantekAuthor Commented:
Ok, where is ''input"
walkthrough please...:o)
Rey Obrero (Capricorn1)Commented:
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
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

brantekAuthor Commented:
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.

r_johnstonCommented:
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.
brantekAuthor Commented:
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_DM_History_Button_Click:
    Exit Sub

Err_DM_History_Button_Click:
    MsgBox Err.Description
    Resume Exit_DM_History_Button_Click
   
End Sub

Is this where I am suppose to start?

Rey Obrero (Capricorn1)Commented:
<<< 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.
brantekAuthor Commented:
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.

Rey Obrero (Capricorn1)Commented:
is the record source of your form the table? if it is replace it with the query that calls for the input.
brantekAuthor Commented:
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.

Rey Obrero (Capricorn1)Commented:
try entering the po number when the box appears and hit ok. what happens?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brantekAuthor Commented:
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)

r_johnstonCommented:
Heh, just wonderin...was that code of any use whatsoever??
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.