Button to open a report to specific record

Question

Button to open a report to specific record

Patrick Snyder 5

I asked this question prior but just wanted to start with a new thread.

I created a report but of course it displays all of the records from the data table. I want a button on my main form that prompts the user to enter a ID, like a primary key, but the fiedl that i am trying to used has number and letters combination and then populate the report with that single record from the data table..

I can get it to work with just the primary key using the following:

Private Sub Command19_Click()
    Dim vID As Variant
    Dim strWhere As String

    vID = InputBox("Enter the ID to display on the report:", "Enter Record ID")

    ' If user cancels or leaves it blank - do nothing
    If vID = "" Then Exit Sub
    If Not IsNumeric(vID) Then
        MsgBox "Please enter a valid numeric ID.", vbExclamation
        Exit Sub
    End If

    'strWhere = "[RFQ_num] = " & CLng(vID)
    
    strWhere = "[RFQ_num] = '" & vID & "'"
    
    DoCmd.OpenReport "MCT Report", acViewPreview, , strWhere
    
End Sub

So, what do I need to do to change it from the just a number field to a number/letter combination field. Examples: 765TR-123 or YT567 or 12345, or ABC-1.

3 answers

Your answer

Answer 1

Duane Hookom 26,625 Volunteer Moderator

Stating “did not work” isn’t very helpful. We can’t see the results or error message, or whatever. I would add some debugging like

    strWhere = "[RFQ_num] = '" & vID & "'"
    Debug.print strWhere   'Take a look at the value
    DoCmd.OpenReport "MCT Report", acViewPreview, , strWhere
End Sub

In addition, you could paste this where string into the SQL view of a query to help troubleshoot.

Also, I would not use an InputBox(). A combo box has many advantages.

Patrick Snyder 5 Reputation points

2026-02-03T20:53:52.13+00:00

I don't want a combo ox because I'm going to have hundreds if not thousands of records. It is far easier to just enter a number that i am looking for then scrolling through a combo box.

As far as not working, i stated above what i wanted and the AI solution did not work. I said that it did not work on alphanumeric values.
Duane Hookom 26,625 Reputation points Volunteer Moderator

2026-02-03T22:55:39.9633333+00:00

The answer provided by AI is the standard solution and should work. That's why I suggested some basic troubleshooting methods that all of us who provide support this forum use when we have issues. Maybe you could post your actual code and perhaps some results of debugging?
Patrick Snyder 5 Reputation points

2026-02-04T11:57:57.7166667+00:00

Duane, see above, i've already posted the code that i was trying to us and the code that the AI used. Debugging??? It doesn't work. it will get the result for numbered values, not alphanumeric values.
George Hepworth 22,415 Reputation points Volunteer Moderator

2026-02-04T12:16:22.6866667+00:00

Another reminder might be helpful. Telling us "it doesn't work" is not the kind of information needed to help you troubleshoot. What is helpful is to SHOW us an actual error message, or explain a) what you expect, and b) more importantly, what actually occurred. That information helps us focus on possible reasons and solutions.

I get it; you think you've been clear. Unfortunately, because there are so many nuances in creating custom VBA, it's often quite difficult to extract useful information from "it doesn't work for alphanumeric values". WHY it doesn't work is determined, in part, by details about what does happen instead.

With regard to the user-friendly input approach of a free-form text box versus a combo box. The issue here is that the input text box requires users to know and correctly type "number and letters combinations". The burden is on the user to do both.

With a combo or list box, the computer does the heavy lifting, not the user. It offers a list of actual values in the table to be searched. With the option "Auto Expand" enabled, the list will respond to each character as it is typed, leading the user down the list to matching values until the one they want appears. If the user does mistype along the way, the matching stops, alerting the user to the mistyped character.

If there are literally thousands of values in the list, sometimes you can assist the user with a "Cascading Combo Box" technique that prefilters using some hierarchy. Since we don't know what your field represents, I can't offer a specific example, but some things spring to mind. Perhaps your records can be grouped by a department or division responsible, or by a customer, or by a product or product line. Selecting that prefilter then limits the list in the final combo box accordingly. It still acts as described above, matching remaining IDs as the user types.

Think about ways to ease the burden on your users by taking advantage of the capabilities of the computer to recall instantly massive amounts of data and manipulate it to make the user's task less of a burden.
Patrick Snyder 5 Reputation points

2026-02-04T13:08:23.23+00:00

Guys, this seems so simple. There is no error message, it just doesn't do what it's is supposed to do.

As in the examples that I provided above "765TR-123 or YT567 or 12345, or ABC-1.", I want a pop-up box that after I click a button that prompts the user to type in a value. The code should be able to go to a field and search and return wherever a record meets or contains a those values. If I enter "123" and there are records 123-345, AB123, or 123-AB, then all three records should be returned. The current code will only return exact matches.

If you want to provide me details of how to setup the cascading combo box, I'll give it a try.
George Hepworth 22,415 Reputation points Volunteer Moderator

2026-02-04T14:07:52.4633333+00:00

Thank you. This is the missing piece we were asking for, albeit you did indirectly reference it before. Unfortunately, it was not clear to me that you were looking for partial matches, not exact matches. Try this:

strWhere = "[RFQ_num] Like '*" & vID & "*'" [Corrected from = to Like with wildcars.]

[edit] This forum's editor messes with the *s and surrounding quote marks from my post. There should be no spaces between * and " [/edit]

Insert the wild card character * before and after the partial string to look for "in-string" or partial matches instead of exact matches.

Here's a good YouTube video on Cascading Combo Boxes.
https://youtu.be/TDEKoXUVaOA?si=EmU6E8iAcRqh1DC7

Here's a good article:
https://www.fmsinc.com/MicrosoftAccess/Forms/combo-boxes/cascading.html
Duane Hookom 26,625 Reputation points Volunteer Moderator

2026-02-04T18:43:48.2033333+00:00

I believe the code should use "Like" rather than "=".

strWhere = "[RFQ_num] Like '" & vID & "'"

Answer 2

Patrick Snyder 5

To anyone that is reading this thread, the answer from the AI did not work on alphanumeric values.

Answer 3

To modify your existing code to handle a combination of numbers and letters for the ID, you are already on the right track with your strWhere assignment. The key is to ensure that you treat the ID as a string, which you are doing correctly by enclosing it in single quotes. Here's the relevant part of your code:

strWhere = "[RFQ_num] = '" & vID & "'"

This line constructs the strWhere string correctly for IDs that contain both letters and numbers. Since you are using single quotes around vID, it will work for values like 765TR-123, YT567, 12345, or ABC-1.

You do not need to change anything else in your code for it to work with alphanumeric IDs. Just ensure that the RFQ_num field in your database is of a text type (not numeric) to accommodate these values.

Here is your complete button click event with the necessary checks:

Private Sub Command19_Click()
    Dim vID As Variant
    Dim strWhere As String

    vID = InputBox("Enter the ID to display on the report:", "Enter Record ID")

    ' If user cancels or leaves it blank - do nothing
    If vID = "" Then Exit Sub

    ' No need to check if numeric since it can be alphanumeric
    ' Just ensure the input is not empty

    strWhere = "[RFQ_num] = '" & vID & "'"
    DoCmd.OpenReport "MCT Report", acViewPreview, , strWhere
End Sub

This should allow your report to open with the specific record based on the ID entered by the user, regardless of whether it is purely numeric or alphanumeric.

Share via

Button to open a report to specific record

3 answers

Your answer