Sort and Display record code

[sphynx]

Hi I have coded one of my forms in a database to search a query via a unique process number entered into a box within the form.

If the process number in the form matches one in the query,the relevant query record is displayed. If no match is found a message box telling the user that no match can be found is displayed.

This seems to work for 75% of the time, but on occasions is displaying records that don't match the number in the form. I have posted my VBA code below.

I am fairly competent with excel visual basic but am still learning in access so please bear with me!


Private Sub Command75_Click()
On Error GoTo REASON_Err

DoCmd.OpenQuery "RESULTS", acViewNormal, acReadOnly

DoCmd.FindRecord Forms![OGW DATA ENTRY]!PROCESS_ID1, acEntire, True, , True, acCurrent, True

DoCmd.RunCommand acCmdFilterBySelection
DoCmd.MoveSize 25, 25, 17500, 1500

MsgBox "PROCESS ID DOES NOT MATCH A WASTE RECORD", vbOKOnly
Call Command78_Click

REASON_Exit:
Exit Sub

REASON_Err:
MsgBox Error$
Resume REASON_Exit

End Sub

Private Sub Command78_Click()
DoCmd.Close acQuery, "RESULTS", acSaveNo
End Sub

 

[Call_Me_Sam]

DoCmd.OpenQuery "RESULTS", acViewNormal, acReadOnly

DoCmd.FindRecord Forms![OGW DATA ENTRY]!PROCESS_ID1, acEntire, True, , True, acCurrent, True

DoCmd.RunCommand acCmdFilterBySelection
DoCmd.MoveSize 25, 25, 17500, 1500

Hi sphynx, why not open the query based on the filter selection of the form, instead of opening the form then filter and causing the error you're finding...can you elaborate more why you are doing it the way you are?

 

[sphynx]

That sounds more logical to do it that way!

I am fairly new to VBA Access and teaching myself as I go, therefore the code I have used is just the way in which I have managed to achieve what I wanted.

Please could you post the appropriate commands so I can try the way in which you have suggested.

Your help is much appreciated

 

[Call_Me_Sam]

That sounds more logical to do it that way!

I am fairly new to VBA Access and teaching myself as I go, therefore the code I have used is just the way in which I have managed to achieve what I wanted.

Please could you post the appropriate commands so I can try the way in which you have suggested.

Your help is much appreciated

Its fairly stragith forward.

Create a query, in the criteria field use the Build.. wizard to set the criteria to that of the unique number.

When user click button, code:

DoCmd.OpenQuery "[I][B]name[/B][/I]", acViewNormal, acReadOnly

If there are no matching records then the query will be blank.

An extension to this could be to check to see if there will be any records displayed, in code, then open the query..or actually display the query in a subform. It really depends on why you are displaying records. If it is a simple query then there is the danger the user could change data, this may be your intention (the readonly in the code should stop them doing that). But a subform looks better on an interface...

 

[sphynx]

Easy when you know how; works a charm like that just get my blank query pop up if there is no matching record.

I still cant get my message box to only display when there is no result in the query would you be able to point me in the right direction on this also?

Again thanks for the help

 

[Call_Me_Sam]

I still cant get my message box to only display when there is no result in the query would you be able to point me in the right direction on this also?

Like i said, you'll need to check the number of records being selected by the query to ascertain if the messagebox needs to be shown.

Try this inside of the button sub:

Dim intCount As Integer
intCount = DCount("[[I][B]fieldname[/B][/I]]", "[[I][B]tablename[/B][/I]]")
If intCount > 0 Then
    DoCmd.OpenQuery "[I][B]tablename[/B][/I]", acViewNormal, acReadOnly
Else
    MsgBox "No Records"
End If

replace the bold italics, let me know how you get on.

 

[sphynx]

that works great, thanks a lot for your help and patience!!!

 

[Call_Me_Sam]

that works great, thanks a lot for your help and patience!!!

hey..i have alot of time on my hands..these old Imperial Destroyers pretty much drive themselves...glad i could help..