Allow only 8 characters in Query Search/Parameter box

[hardhitter06]

Hey Guys,

I'm using Access 2003.

I feel like this question is rather simple, but you never know. I have a query search set up for a user to enter an account number to get results.

All of our account numbers are 8 characters long so I want to make the Query Search Pop Up Input Box (or that parameter box, whatever you want to call it that allows you to search a query) to only allow the user to search with exactly 8 characters. How do I do this??

 

[pbaldy]

You don't have much control over that box. You'll need to create a form with a textbox, and run your search from there. You can test the length of the textbox before proceeding.

 

[hardhitter06]

Is this the only way everyone??

 

[MSAccessRookie]

Is this the only way everyone??

pbaldy is correct (as usual). It is the best way to get what it seems that you want.

You could also evaluate the results of the popup box and reject them if they do not meet the requirements, but this does not appear to be what you are looking for.

 

[hardhitter06]

Alright I just wanted a second opinion. Thank you both

 

[pbaldy]

You just don't have enough control over the parameter box that pops up when you uses

[Enter whatever]

in a query criteria. You'll find most experienced developers don't use it for that reason. Most of us use forms to gather user input so we can perform whatever validation is appropriate to the situation.

 

[hardhitter06]

Ok, I have decided to try this out. I will probably start tomorrow. We'll see how I make out. Thanks again for your help!

 

[hardhitter06]

Hi Again,


I'm not really sure how to make a form with a searchable field in it. Can someone plz help me?

 

[twoplustwo]

Use a text box on a form. Enter this is in the criteria field of the query: [Forms].[FormName].[TextBoxName]

 

[hardhitter06]

I'm still a little confused because that didn't work but I feel like I'm leavin somethin out. I made the change to the query criteria. I'm using Form1 as the search form/field and I want it to open the Report on my dashboard after I search through the form. I attached my database, use UNSPSC code 11112222 if you can help me further with this.

Thank you!

 

[twoplustwo]

Try the attached.

I assume you want to search on codes you already have.

Select the relevant on from the combo box and open the query. Using the combo eliminates user input.

Get back to me if this doesn't match your needs imo.

 

[hardhitter06]

I don't know if we are on the same page. I wanted to be able to search this code with exactly 8 characters so I was told that I needed to add a field to a form to do the search. I don't want a drop down box, I just want to be able to enter in the 8 digits and search the given record unless this isn't possible and I do need to use a drop down box. If that's the case, I clicked on a UNSPSC code and hit Search from the dash and that returned all records....

 

[twoplustwo]

You can do that it is just a waste of time!

Also, if you start typing in the combo box it finds the one you want anyway.

Honestly, this method is far quicker/easier/efficient.

And I forgot to alter the query!! Apologies.

 

[hardhitter06]

It would help prevent user error though..so I don't feel like it's a waste because if the user selects the wrong one, or somehow errors down or up you never kno...

 

[twoplustwo]

It's kinda the opposite to preventing error!

Anyway. If you really insist on it just delete that in design view, create a text box and in your query change "Combo4" to the name of your new text box.

Then, in the click event of your button to open the query add this code:

Dim strIdNumber As String
 
strIdNumber = me.YourTextBoxName
 
If Len(strIdNumber ) <> 8 Then
    MsgBox "Incorrect ID, re-enter.", vbOKOnly, "Error"
    Exit Sub
End If

 

[hardhitter06]

Am I understanding this correctly, please bare with me,

I created a form, with a text field named Text0, here is my on click code for this field:

Private Sub Text0_Click()
Dim strIdNumber As String

strIdNumber = Me.Text0

If Len(strIdNumber) <> 8 Then
MsgBox "Incorrect ID, re-enter.", vbOKOnly, "Error"
Exit Sub
End If
End Sub.

And this is what is in my query under UNSPSC field in criteria: [Forms].[FormName].[Text0]

Now with that said, and I don't even kno if im right up to there but I need this search to open my rptMain with the given UNSPSC code and records

 

[hardhitter06]

Someone plz help me with this...I feel this isn't that hard, but I was confused by the guy helping me before...

 

[pbaldy]

Personally I would put that code behind the button that opens the form or report that uses that query. If the input passes your test, open the form/report, else give the user a message box. In other words, your form would have the textbox for user input and a button for them to push to continue.

 

[hardhitter06]

Pbaldy,

Can you show me how to arrange the code because I already had some in there and I'm not really good at coding.

Private Sub Command2_Click()
Dim strIdNumber As String

On Error GoTo Err_Command2_Click

Dim stDocName As String

stDocName = "OpenReportMain"
DoCmd.RunMacro stDocName

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub

where should this go:

strIdNumber = Me.Command2

If Len(strIdNumber) <> 8 Then
MsgBox "Incorrect ID, re-enter.", vbOKOnly, "Error"
Exit Sub
End If

 

[pbaldy]

The essential bits:

If Len(strIdNumber) <> 8 Then
  MsgBox "Incorrect ID, re-enter.", vbOKOnly, "Error"
Else
  DoCmd.RunMacro stDocName
End If