Parameter Query

[jojo86]

I have a search button on my menu form that, when clicked, asks the user to enter a desired Reference number they wish to search for. This works fine when it is a Reference number that is stored within the database.

I have tested it to see if it works with reference numbers that do not exist, and instead of giving an error message to ask them to retry, it opens up the form to a new record... That is not what I want it to do.

Please can someone help me, here if my code up to yet (not sure if the msgbox is correct btw):

Private Sub cmdSearchSite_Click()
On Error GoTo Err_cmdSearchSite_Click

    DoCmd.OpenQuery "qrySearchRTP" 
    DoCmd.OpenForm "FRM_PRIMARY" 
    DoCmd.GoToRecord acDataForm, "FRM_PRIMARY"
    DoCmd.Close acQuery, "qrySearchRTP" 
    DoCmd.Close acForm, "FRM_MENU"

Exit_cmdSearchSite_Click:
Exit Sub

Err_cmdSearchSite_Click:
MsgBox ("Site not found. Please enter a valid RTP Reference Number")
Resume Exit_cmdSearchSite_Click
    
End Sub

 

[Michael J Ross]

Hi,

What you need to do is to test whether the Reference No the user enters actually exists before running the query.

I would do this using DCount and if the count is 0 i.e. that reference number doesnt exist, give the message box message and not run the query or open the results form.

Add the following 2 variables to your code:

Dim intcount as Integer
Dim strcriteria As string

Add the following lines to your code adding your table, field names and textbox control names from the form the user enters the ref no in as appropriate

strcriteria = "Name of Your refernce number field= " & "'" & textBoxname & "'"
intcount = DCount("[Name of Your refernce number field]","YourTableName", strcriteria)

If intcount > 0 Then

DoCmd.OpenQuery "qrySearchRTP"
DoCmd.OpenForm "FRM_PRIMARY"
DoCmd.GoToRecord acDataForm, "FRM_PRIMARY"
DoCmd.Close acQuery, "qrySearchRTP"
DoCmd.Close acForm, "FRM_MENU

Else
MsgBox ("Site not found. Please enter a valid RTP Reference Number")

End If


I hope this helps you.

 

[jojo86]

I think it would work, but stupid me has a table name with spaces in it, and I know you shouldn't have spaces in the name, but I don't know whether I can change that now, I have done so much and most of this database works...

The table name for the second argument in the DCount is "Tab 1: Factual". How shall I write this in to the code?

Thanks for helping me (yes I am just starting out at Access btw)

 

[Michael J Ross]

Hi,

You could try it exactly as you have typed it or by enclosing it in [] brackets.

Your welcome, post back any problems you get.

 

[jojo86]

An error message now appears - "Run-time error '3464': Data type mismatch in criteria expression" and when I click Debug, it highlights the intcount = line.

This is my code, I have done exactly what you asked (RTP_ID is the name of the field and text box, Tab 1: Factual is the name of the table):

Private Sub cmdSearchSite_Click()

Dim intcount As Integer
Dim strcriteria As String

strcriteria = "RTP_ID= " & "'" & RTP_ID & "'"
intcount = DCount("[RTP_ID]", "[Tab 1: Factual]", strcriteria)

If intcount > 0 Then

DoCmd.OpenQuery "qrySearchRTP"
DoCmd.OpenForm "FRM_PRIMARY"
DoCmd.GoToRecord acDataForm, "FRM_PRIMARY"
DoCmd.Close acQuery, "qrySearchRTP"
DoCmd.Close acForm, "FRM_MENU"

Else
MsgBox ("Site not found. Please enter a valid RTP Reference Number")

End If

End Sub

Thanks for helping me

 

[Michael J Ross]

Hi,

Is RTP_ID a numeric field? If so you can get rid of the quotes around
"'" & RTP_ID & "'"

I think you'll also need to change it slightly to Me.RTP_ID

 

[jojo86]

Sorry about this.

I have done what you suggested, and it still comes up with the same error.

RTP_ID is an autonumber field (so numeric, yes). I have changed it to "' & Me.RTP_ID & '" but still no luck.

 

[Michael J Ross]

It's Ok we'll get there.

Change strcriteria to the following,

strcriteria = "RTP_ID= " & Me.RTP_ID

 

[jojo86]

I have done that, and a new error now appears.
Compile Error: Method or data member not found

It now highlights in yellow to Private Sub line, and then highlights in gray .RTP_ID.

 

[Michael J Ross]

Ok, we will get there I promise!

It appears its not recognising your text box name.

Is it possible for you to attach a cut down version of your mdb (with perhaps only one record in your table and the form in) so I can see it?


Below is the code of a sample DB ive created based on your names that works ok.

Dim stDocName As String
Dim intcount As Integer
Dim strcriteria As String

strcriteria = "RTP_ID= " & Me.RTP_ID
intcount = DCount("[RTP_ID]", "[Tab 1: Factual]", strcriteria)

If intcount > 0 Then

stDocName = "Query1"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Else
MsgBox ("Doesn't exist try again")
End If

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub

 

[jojo86]

I tried putting in that code you gave, and it still comes up with the error. I have attached a copy of my db, the form to open is FRM_MENU and the button states "Search for a site" (or something along those lines).

Thanks for this.

 

[Michael J Ross]

I know what the problem is, I thought you were entering your search criteria into a form, but youre putting it into the query directly so the code doesn't work.

What you need to do is to create a form the user enters the criteria in and create a button that has the code in there rather than on your menu button. Your menu button should just open up the form to enter the criteria.
You will need to cross reference the query to your new form and remove the prompt from your criteria.

If you can wait until this evening I'll post an example if you don't understand.

 

[jojo86]

thank you!!! You are a star! If you don't mind, I will wait until the evening for your example. This database has been driving me nuts, everything seems to be not working the way it should, and I am a novice at doing access on my own.

 

[Michael J Ross]

Hi,

I've attached a version of your mdb with a working search.

I have created a new from called FRM_SEARCH which is triggered when you click the Search for site button, and I've also cross referenced the query to FRM_SEARCH.

Enter the site you are searching for in the box and click the command button, if the site doesnt exist the message box appears, if it does exist the query runs and the form opens.

It appears the mdb has been corrupted when I've saved and uploaded it, if you copy FRM_SEARCH and the query into your mdb it should hopefully work, you'll need to change the on click event of the Search for site button on your menu so that it opens FRM_SEARCH. Sorry about that I don't know whats gone wrong

One thing I noticed is the from is based on your table and opens up at the last record in the table even if site 1 is selected in the query. You need to base your form on the query rather than the table I think to display the correct results.

It's frustrating when things don't work as they should but its the best way of learning!

It looks quite a challenging project, Good Luck.

Hope this helps get back with any queries

 

[Michael J Ross]

This one should work.

 

[jojo86]

Yay! The search bit works! That's cool. Thank you for that!

Now, all I need to know now is how to sort out that problem of it going to the very last record... I am not sure on how to do it. Also, I would like to search for postcode too, but when I try it with the same type of code, it either says "Syntax Error (missing operator)... for postcode..." (as my table has postcode with an input mask of >LL0&\ 0LL;;* to make it into a proper postcode for the area) or it says "You have cancelled this operation", which i haven't.

Thanks again Michael

 

[Michael J Ross]

Yay! The search bit works! That's cool. Thank you for that!

Now, all I need to know now is how to sort out that problem of it going to the very last record... I am not sure on how to do it. Thanks again Michael

You're welcome glad it worked!

To solve this I think you will need to create a copy of your FRM_PRIMARY and set it's source to the Query, that way it will only bring back the selected record.

Also, I would like to search for postcode too, but when I try it with the same type of code, it either says "Syntax Error (missing operator)... for postcode..." (as my table has postcode with an input mask of >LL0&\ 0LL;;* to make it into a proper postcode for the area) or it says "You have cancelled this operation", which i haven't.

Can you post what youve done for this?

 

[jojo86]

How can you make a form as a source of a query? - it only lets me do tables (in design view of query making, i am assuming that's what you mean).

This is the code behind the search button for postcode (maybe the errors are arising because it's a DCount thing???):
Private Sub cmdSearchPC_Click()
On Error GoTo Err_cmdSearchPC_Click

Dim stDocName As String
Dim intcount As Integer
Dim strcriteria As String


strcriteria = "POSTCODE= " & Me.txtSearchPC
intcount = DCount("[POSTCODE]", "[Tab 1: Factual]", strcriteria)

If intcount > 0 Then

stDocName = "qrySearchPC"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.OpenForm "FRM_PRIMARY"
DoCmd.GoToRecord acDataForm, "FRM_PRIMARY"
DoCmd.Close acQuery, "qrySearchPC"
DoCmd.Close acForm, "FRM_SEARCH"

Else
MsgBox ("Site not found. Please enter a valid Postcode")
End If

Exit_cmdSearchPC_Click:
Exit Sub

Err_cmdSearchPC_Click:
MsgBox Err.Description
Resume Exit_cmdSearchPC_Click

End Sub

Thanks

 

[Michael J Ross]

How can you make a form as a source of a query? - it only lets me do tables (in design view of query making, i am assuming that's what you mean).

No make the query the source of your form, Copy your FRM_PRIMARY and set the source of the forms to the query (your query will need to have all fields in that appear in the forms). In Design View of the Form view its properties and click the Data Tab, change the record Source to qrySearchRTP. Change the code so it opens up the copy form not the exising FRM_PRIMARY.

Ok for the post code search, the post code is a text field , therefore we need to amend strcriteria to

strcriteria = "POSTCODE= " & "'" & Me.txtSearchPC & "'", you should also set the the inputmask of the txtSearchPC to the post code so that it only accepts data in post code format.

 

[jojo86]

New error... Sorry

I have copied FRM_PRIMARY and called it FRM_SEARCH_P (meaning its the search form for the form primary). I entered a site that exists, and it showed the site, but in most of the field boxes on the main form, it showed #Name? in them instead... Confused!

And will that new form get updated once more sites are added to the database, or will they have to be put in twice?