X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Loop Through A Recordset, Access 2010    
post May 16 2019, 02:40 AM

Posts: 3
Joined: 22-March 19

I've got a form with a search button, a textbobx1 and a textbox2. I also have a table tbTransaction.
When I enter a part number into textbox1 and press search button, I would like Access to 'loop through' the table tbTransaction and 'print out' all the instacnes of that part number in the textbox2.

I came up with something like this:

Set MyDB = CurrentDb
Set rs = MyDB.OpenRecordset("tbTransaction", dbOpenDynaset)

If DCount("*", "tbTransaction", "[Part Number] = '" & Me.txtPN1 & "'") > 0 Then

>>>>>>> Here is where the loop should be I guess <<<<<<<<<<<<<<<<<<<<<<<<<<<<

Set rs = Nothing

Any help appreciated as to what I should put in the vba code above.
Go to the top of the page
post May 16 2019, 02:50 AM

Posts: 523
Joined: 25-January 16

You want to concatenate values from related records? Review http://allenbrowne.com/func-concat.html

This is usually done for a report output. Why do you need this in a form?

This post has been edited by June7: May 16 2019, 02:51 AM

Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
post May 16 2019, 05:17 AM

Posts: 1,352
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.

If your opening a record set you do not need to have a dcount and should let the record set do the work.

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    strSql = "Select * from tbTransaction where [Part Number] = """ & Me.txtPN1 & """"
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)

    If rs.BOF And rs.EOF Then
        GoTo MyExit
    End If

    Do Until rs.EOF

        Debug.Print rs![Part Number]


    Set rs = Nothing
    Set db = Nothing

Go to the top of the page
post May 16 2019, 07:10 AM

UtterAccess VIP
Posts: 7,878
Joined: 24-May 10
From: Downeast Maine

What do you mean "print out all instances of that part number"? Why print the same part number several times? Why not just count it?

Is the part number itself stored in the table, or is there a part number table and you are storing just the key field for the part number record?
Go to the top of the page
Jeff B.
post May 16 2019, 07:19 AM

UtterAccess VIP
Posts: 10,242
Joined: 30-April 10
From: Pacific NorthWet

If you'll describe a bit more about your scenario, and why you want to do this, it would help us offer more relevant suggestions.

For example, if the underlying business need is to see records related to a search term (?part number), what about the idea of using a subform that gets 'filled' by the records that match your search term? You could use a simple SQL statement (SELECT A, B, C FROM YourTable WHERE PartNumber = [textbox1]) as a record source for that subform.


Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
post May 16 2019, 07:43 AM

UtterAccess VIP
Posts: 10,729
Joined: 10-February 04
From: South Charleston, WV

You could make a form that lists tblTransaction, put textbox1 and the button in the form header, and just filter the form.

Robert Crouser
Go to the top of the page
post May 16 2019, 08:33 AM

Posts: 2,297
Joined: 4-February 07
From: USA, Florida, Delray Beach

  1. All of the options provided are better than that which you currently have, but if you still want your Request to be taken literally, the following will do the trick (assumes the 2nd Text Box for the results is named txtResults):
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strBuild As String

    strSQL = "SELECT * FROM tblTransaction WHERE [Part Number] = '" & Me![txtPN1] & "'"

    If IsNull(Me![txtPN1]) Then Exit Sub    'if Nothing, then exit
    Me![txtResults] = ""                    'Clear Results Text Box

    Set MyDB = CurrentDb
    Set rst = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)

    With rst
      Do While Not .EOF
        strBuild = strBuild & ![Description] & vbCrLf
    End With

    If strBuild = "" Then
      Me![txtResults] = ""
      Me![txtResults] = Left$(strBuild, Len(strBuild) - 2)   'strip vbCrLf
    End If

    Set rst = Nothing
  2. Example of possible OUTPUT (excluding the Part Number):
    Sent to Shop for Maintenance on May 1, 2019
    Back in service on May 12, 2019
    Replaced defective unit

This post has been edited by ADezii: May 16 2019, 08:35 AM
Go to the top of the page
post May 16 2019, 08:08 PM

UtterAccess VIP
Posts: 2,810
Joined: 12-April 07
From: Edmonton, Alberta Canada

You could cobble together a loop and code to do this.

But why not build a report based on that transaction table that lists out all of the data you have.

Then in code on your form?

Well, assuming one text box, and a button?

Your code would look like this:

Private Sub Command65_Click()

   Dim strWhere      As String
   strWhere = "[Part Number] = '" & Me.txtPN! & "'"
   DoCmd.OpenReport "rptTrans", acViewPreview, , strWhere
End Sub

So with above:
No need to write looping code.
You can then print the report, email it, save as pdf. All of these things are RATHER painful with your form and attempting to fill out a text box.

Perhaps you question is in regards to just learning more VBA, but if you goal is to list out the matching part numbers, then as above shows, this is oh so very easy, and results in a nice looking report to boot.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    19th May 2019 - 08:22 PM