On no data in form 2

[hdgirl]

I have tried this before but still can't solve it.

I have an input box that allows a use to select a record by inserting a record number that opens another form showing all details regarding that record number, if a number is inserted that doesn't exist then the 2nd form will open at a blank record, how can i get a message box to open saying "no record exists for that number" or something like that ? CJB

 

[nealvenables]

Hi,

You could prevent the user selecting a blank record in the first place by changing the "select record" textbox to a combobox or listbox.

This is a much more user-friendly approach .

Cheers.

 

[hdgirl]

I have tried this but there are far too many records i would prefer the user to input the auto number rather than search for it, but thanks anyhow for your response CJB

 

[nealvenables]

You could try putting something along these lines behind the command button that opens the second form (posted on the fly, so some of the syntax will be wrong - should give you the general idea though...)

private sub MyCommandButton_Click()

	dim CNN as new adodb.connection 
	dim RST as new adodb.recordset
	dim SQL as string

	CNN.open currentproject()
	RST.activeconnection = CNN

	SQL = "SELECT MyID FROM MyTable WHERE MyID = " & MyTextBox

	set RST = CNN.execute (SQL)

	if RST.EOF then
		msgbox "No Data to display"
	else
		docmd.openform "frm2"

	endif

	RST.Close
	set RST = nothing
	set CNN = nothing

end sub

Good luck...

 

[paulbasi]

Hi there, I have had the same situation where if there were no records to display only a blank form would show up. To resolve this, I created a bitmap with text that explained to the user there were no records and then used that bitmap as the background for the form. Now, if there were records, the data would cover the bitmap. If there were no records, then the bitmap would show through informing the user.

Hope this helps,
Paul

 

[Bry12345]

As an alternative to NealV's suggestion, if you don't want to use a recordset, you can do a DCount on the query which is used to open the form. If Dcount <1 Then MsgBox &quot;No Records or Something Meaningful&quot;, Else Open the Form.

You stated that you have a fair amount of records. This technique runs the query twice, which may cause a performance issue with a lot of records. - - - -

Bryan

 

[hdgirl]

I have tried all the above and i am still getting nowhere so i will post the code that i am using

Private Sub cmdOpenRec_Click()

Dim strWorksOrderNumber As String, strFilter As String
strWorksOrderNumber = InputBox(&quot;Please Enter W/O Number&quot


If strWorksOrderNumber = &quot;&quot; Then

GoTo Exit_This_Sub
End If

strFilter = &quot;[WorksOrderNumber] = &quot; & Trim(Str(strWorksOrderNumber))
DoCmd.OpenForm &quot;frmAdmin&quot;, acNormal, , strFilter
Exit_This_Sub:
Exit Sub

End Sub

If i remove the goto statement and insert a messagebox when i enter no data the message box appears but not if i insert a works order number that doesn't exist

Any help would be appreciated
Thanks CJB

 

[Bry12345]

HD,

What is happening makes sense, because you are only testing for a a null or no data with: 'If strWorksOrderNumber = &quot;&quot; '

Try this:

1 - Design a query (qryYourQry) that will test for the presence of a record meeting the criteria entered into your InputBox

2 - The DCount below will run the query and count the records that match the criteria. Based on that, you can decide the appropriate actions for when matching records exist and when none exist.

- - -

If DCount(&quot;*&quot;, &quot;qryYourQry&quot > 0 Then
'Do whatever when you have at least one match

Else
'Do whatever when there is no match

End If

- - -

Let me know if this is not exactly on point . . we can tweak it. - - - -

Bryan

 

[MacDaddyNickP]

What I do is the following:

Private Sub cmdViewDefects_Click()
On Error GoTo Err_cmdViewDefects_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim intCountRecs As Integer


stDocName = "frm_Bldg_ExtWalls_Defects"
stLinkCriteria = "[BLDG_NBR]=" & "'" & Me![txtBLDG_NBR] & "'"

intCountRecs = DCount("[BLDG_NBR]", "[BLDG_EXTERIOR_WALL_DFCT]", stLinkCriteria)
If intCountRecs <> 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "There are no Defects Reported.", vbOKOnly, "No Records to Display"
End If

Exit_cmdViewDefects_Click:
Exit Sub

Err_cmdViewDefects_Click:
MsgBox Err.Description
Resume Exit_cmdViewDefects_Click

End Sub