[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

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
0
brantek
Asked:
brantek
  • 6
  • 5
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
use the input to filter the records of the form

docmd.openForm "formname", , "[po#]=<po # input>"
0
 
brantekAuthor Commented:
Ok, where is ''input"
walkthrough please...:o)
0
 
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
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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.

0
 
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.
0
 
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?

0
 
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.
0
 
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.

0
 
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.
0
 
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.

0
 
Rey Obrero (Capricorn1)Commented:
try entering the po number when the box appears and hit ok. what happens?
0
 
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)

0
 
r_johnstonCommented:
Heh, just wonderin...was that code of any use whatsoever??
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now