Form Loading Question 2

[robcarr]

HI,

I am using the coding below and the box appears if the records is not found, but the form, still loads, how do I stop the form loading if the record is not found.

Dim Msg As String, Style As Integer, Title As String
   
   If Me.RecordsetClone.RecordCount = 0 Then
      Msg = "No Records Found!"
      Style = vbInformation + vbOKOnly
      Title = "No Records Notice! . . . ."
      MsgBox Msg, Style, Title
   End If

my query on the form is

SELECT tblItemReceived.ReferenceNumber, tblItemReceived.DateLogged, tblItemReceived.TimeLogged, tblItemReceived.LoggedBy, tblItemReceived.ItemType, tblItemReceived.CustomerRef, tblItemReceived.DateClosed, tblItemReceived.ClosedAs, tblItemReceived.TimeClosed
FROM tblItemReceived
WHERE (((tblItemReceived.ReferenceNumber)=[Enter Reference Number to Find]));

Hope this is of use, Rob.

 

[LNBruno]

Have you tried sticking an "application.quit" statement in there?

< M!ke >

 

[ZmrAbdulla]

LNBruno, "Application.Quit" will close the entire Database.

You need Exit Sub instead.



________________________________________
Zameer Abdulla
Visit Me
The best thing to spend on your child is your time.

 

[AnnabelleMarie]

Use the Unload statement in your if statement. If you have no records, then unload form. Hope this helps.

 

[robcarr]

tried to use an unload statement, as below, but cannot get it to work says
cant load or unload this object

the form is blank no fields on it.

Private Sub Form_Load()
Dim Msg As String, Style As Integer, Title As String
   
   If Me.RecordsetClone.RecordCount = 0 Then
      Msg = "No Records Found!"
      Style = vbInformation + vbOKOnly
      Title = "No Records Notice! . . . ."
      MsgBox Msg, Style, Title
      
      Unload Forms!frmclosecases
   End If

thanks for suggestions so far

Hope this is of use, Rob.

 

[RoyVidar]

When opening a form, I think the on open event is the only one where you can cancel the opening (it has a cancel parameter you where you can use: cancel = true within the routine).

That said, I think I'd redo this, if possible, to enter this parameter in the calling form (here using a text control txtCriterion), and remove the where condition of the sql for the form. Then on click of the button, or whatever you use to trigger this, check if the record exists. Here just doing a Domain Aggregate sample:

[tt]if trim$(me!txtCriterion & "") ="" then
msgbox "enter a value first..."
else
if not isnull(dlookup("ReferenceNumber","tblItemReceived", _
"ReferenceNumber = " & me!txtCriterion) then
docmd.openform "yourform",,,"ReferenceNumber = " & me!txtCriterion
else
msgbox "doesn't exist, try again"
end if
end if[/tt]

- typed not tested

Roy-Vidar

 

[earthandfire]

OnOpen has a Cancel parameter that can be used to close the form before it is displayed. I don't think RecordsetClone would work here though because I think the recordset is applied in the OnLoad event. Therefore you would need to run your query in code

dim rs as recordset
set rs = currentdb.openrecordset(ReleventQueryString)
if rs.bof and rs.eof then
cancel = true
msgbox "Nothing to show"
end if
rs.close
set rs = nothing

if rs has records cancel will be false and the form will open normally, otherwise it should not open


Alternatively, you could put your code in the OnCurrent event and use

docmd.close etc etc (I'm not sure of the parameter order off hand

Although this code would run on each record it would only close the form if there were no records.

Hope this helps

 

[robcarr]

roy,

I have added cancel = true and this works, and stays on the mainform if record isnt found and if record found opens, so this solution is okay,

with regards to your other way, I would use this code on the cmdclosecases button on the mainform? and remove the query option to enter a reference, is this correct. as this way it seems would not generate another message box after the "no records found" msgbox, as the cancel = true does , it says "The openform action was cancelled" click ok

thanks for your help so far.



Hope this is of use, Rob.

 

[earthandfire]

I've just spotted your criteria.

WHERE (((tblItemReceived.ReferenceNumber)=[Enter Reference Number to Find]));

My solution would cause the prompt for a reference number to appear twice, However, you could solve this by having the RefernceNumber picked up from a textbox on the callng form and passed into the new form in the OpenArgs parameter.

 

[RoyVidar]

The cancel thingie (error 2501) can be avoided through errorhandling:

[tt]on error goto myerr

' the sub's code, includig the openform call

myexit:
exit sub
myerr:
' only errormessages for other errors
if err.number <> 2501 then
msgbox err.description
end if
resume myexit
end sub[/tt]

My "other way" involves not opening the form at all, if the record isn't found - i e testing the criterion first, then if the record exist, open the form.

My reason for suggesting this, is what earthandfire also comments on - when there's a user defined criterion in the query, it's difficult to perform such tests without getting the parameter prompt more than once - and then the real fun - users typing different criteria each time;-)

I don't use such parameters at all! cause there's no way you can validate the input, and it's often confusing to the user to retrieve errors or no records when they believe they've entered correct values. I always allow some means of selecting/typing the criterion on the calling form, which makes it possible to validate the contents prior to using it.

Roy-Vidar

 

[robcarr]

Roy,

thanks for your help so far on this,
I ahve decided to use the txtcriterion way using a text box and searching for the file before loading the form, however I get an error on the

if not isnull(dlookup("ReferenceNumber","tblItemReceived", _
               "ReferenceNumber = " & me!txtCriterion) then

it says "The object does not contain the automation object 'MO66." I was searching for mo66, and it does exist.

any ideas.


Hope this is of use, Rob.

 

[RoyVidar]

Two things I can see
* I missed a closing parens
* this is a text, so single quotes

[tt]if not isnull(dlookup("ReferenceNumber","tblItemReceived", _
"ReferenceNumber = '" & me!txtCriterion & "'")) then[/tt]

Roy-Vidar

 

[earthandfire]

If reference nmberis text the nyou will need

"ReferenceNumber = '" & me!txtCriterion & "'" )

 

[robcarr]

Thanks all this now works, using

If Trim$(Me!txtCriterion & "") = "" Then
MsgBox "enter a value first..."
Else
If Not IsNull(DLookup("ReferenceNumber", "tblItemReceived", "ReferenceNumber = '" & Me!txtCriterion & "'")) Then
DoCmd.OpenForm "frmclosecases", , , "ReferenceNumber = '" & Me!txtCriterion & "'"
Me.txtCriterion = ""
Else
MsgBox "doesn't exist, try again"
Me.txtCriterion = ""
End If
End If

however if a closed case is reopended a blank frmclosecases appears, can I add an additional lookup/if and check to see if closedas is blank.

Hope this is of use, Rob.

 

[robcarr]

I hope this is close, but cannot get it to not open the form if the case has already been closed,

Private Sub cmdclosecases_Click()
If Trim$(Me!txtCriterion & "") = "" Then
MsgBox "enter a value first..."
Else
If Not IsNull(DLookup("ReferenceNumber", "tblItemReceived", "ReferenceNumber = '" & Me!txtCriterion & "'")) Then
DoCmd.OpenForm "frmclosecases", , , "ReferenceNumber = '" & Me!txtCriterion & "'"
If Not IsNull(DLookup("ClosedAS", "Tblitemreceived", "Closedas = '" > " ")) Then
DoCmd.Close
MsgBox "Record has already been closed"
Else
End If
Me.txtCriterion = ""
Else
MsgBox "Record does not exist, plese try another", vbExclamation
Me.txtCriterion = ""
End If
End If


End Sub

Hope this is of use, Rob.

 

[RoyVidar]

One thing, not related to the code per see, but both for you when you come back to it at a later state, to do some amendments, but also for us reading it here - use some indentation on the blocks - it provides so much easier overview of the logic... the conservatives among us (read me;-)) also approve of code fitting nice into the VBE code window (approx 70-80 characters per line)

Using Domain Aggregates, I'd probably placed the second test before the openform thingie.

Now - the purpose of this is, if I understand correctly, is to disallow the opening of the form on two conditions, either
* the inputted value (txtCriterion) doesn't exist
* the "closedas" field of the inputted criterion is something (I didn't quite catch what)

I'm not very fond of the Domain Aggregates, and in this case, testing two values -> recordset, as described by erthandfire:

[tt]dim strSql as string
dim rs as dao.recordset
dim db as dao.database
If Trim$(Me!txtCriterion & "") = "" Then
MsgBox "enter a value first..."
Else
set db=currentdb
strsql="select ClosedAs from tblItemReceived " & _
"where ReferenceNumber = '" & Me!txtCriterion & "'")
set rs=db.openrecordset(strsql)
if not rs.bof and not rs.eof then
' record is found
if trim$(rs.fields("ClosedAs").value & "") = "" then
' field is "blank
else
' field contains info
end if
else
msgbox "didnt exist..."
end if
rs.close
set rs=nothing
set db=nothing
end if[/tt]

Now - whether the openform statement goes into the true or false clause, I don't know... is this closer?

- again, typed not tested, would probably need some errorhandling...

Roy-Vidar

 

[robcarr]

works well, thanks roy, only removed a ")" on strsql and code worked straigh away.

cheers

Hope this is of use, Rob.