A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Return to form if query unmatched



 
 
Thread Tools Display Modes
  #1  
Old June 13th, 2007, 10:06 PM posted to microsoft.public.access.queries
Richard
external usenet poster
 
Posts: 1,419
Default Return to form if query unmatched

I have this query.

SELECT BOOKS.Number
FROM BOOKS
WHERE (((BOOKS.Number)=[Enter the number:]))
GROUP BY BOOKS.Number
ORDER BY BOOKS.Number DESC;

What I want to do now is if the query returns no results, display a message
then go to Main Manu form.

Can this be done?

I am using Windows XP and Access 2000.

Many thanks in advance.
--
Richard
  #2  
Old June 13th, 2007, 10:08 PM posted to microsoft.public.access.queries
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default Return to form if query unmatched

In the form's Open event, you can check whether the query returns any rows.
If it doesn't pop up a message and set Cancel = True.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Richard" wrote in message
...
I have this query.

SELECT BOOKS.Number
FROM BOOKS
WHERE (((BOOKS.Number)=[Enter the number:]))
GROUP BY BOOKS.Number
ORDER BY BOOKS.Number DESC;

What I want to do now is if the query returns no results, display a
message
then go to Main Manu form.

Can this be done?

I am using Windows XP and Access 2000.

Many thanks in advance.
--
Richard



  #3  
Old June 13th, 2007, 11:26 PM posted to microsoft.public.access.queries
Richard
external usenet poster
 
Posts: 1,419
Default Return to form if query unmatched

Thanks Doug

I have done some searching, but can't find out how to do what you are
suggesting. Can you help me further please.

Thank you.
--
Richard


"Douglas J. Steele" wrote:

In the form's Open event, you can check whether the query returns any rows.
If it doesn't pop up a message and set Cancel = True.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Richard" wrote in message
...
I have this query.

SELECT BOOKS.Number
FROM BOOKS
WHERE (((BOOKS.Number)=[Enter the number:]))
GROUP BY BOOKS.Number
ORDER BY BOOKS.Number DESC;

What I want to do now is if the query returns no results, display a
message
then go to Main Manu form.

Can this be done?

I am using Windows XP and Access 2000.

Many thanks in advance.
--
Richard




  #4  
Old June 14th, 2007, 06:36 AM posted to microsoft.public.access.queries
Ko Zaw
external usenet poster
 
Posts: 3
Default Return to form if query unmatched

On Jun 14, 4:26 am, Richard wrote:
Thanks Doug

I have done some searching, but can't find out how to do what you are
suggesting. Can you help me further please.

Thank you.
--
Richard

"Douglas J. Steele" wrote:
In the form's Open event, you can check whether the query returns any rows.
If it doesn't pop up a message and set Cancel = True.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Richard" wrote in message
...
I have this query.


SELECT BOOKS.Number
FROM BOOKS
WHERE (((BOOKS.Number)=[Enter the number:]))
GROUP BY BOOKS.Number
ORDER BY BOOKS.Number DESC;


What I want to do now is if the query returns no results, display a
message
then go to Main Manu form.


Can this be done?


I am using Windows XP and Access 2000.


Many thanks in advance.
--
Richard


Dear Richard,

Let me give you the code what Sir-Douglas talking about.

Private Sub Form_Open(Cancel As Integer)

Dim Mydbs As Database
Dim Myrst As Recordset
Dim MyRecCount As Long
Set Mydbs = CurrentDb
With Mydbs
Set Myrst = .OpenRecordset(Form.RecordSource)
MyRecCount = Myrst.RecordCount

If MyRecCount = 0 Then
MsgBox "This BOOK Number has " & MyRecCount & " no
Record." _
& vbCrLf & "You do not need to view."
Cancel = True
Else

MsgBox "This BOOK Number has " & MyRecCount & "
Record(s)."
End If
End With
Set Mydbs = Nothing
Set Myrst = Nothing

End Sub
__________________________________________________ ________
Ko Zaw

  #5  
Old June 14th, 2007, 08:19 AM posted to microsoft.public.access.queries
Richard
external usenet poster
 
Posts: 1,419
Default Return to form if query unmatched

Thanks Ko Zaw

I have put your code into the Open event on my form, but I am getting the
following error message upon opening the form:

Compile Error:
User-define type not defined

and the following text is then highlighted:
Mydbs As Database

Any ideas?

Thank you for your help.
--
Richard


"Ko Zaw" wrote:

On Jun 14, 4:26 am, Richard wrote:
Thanks Doug

I have done some searching, but can't find out how to do what you are
suggesting. Can you help me further please.

Thank you.
--
Richard

"Douglas J. Steele" wrote:
In the form's Open event, you can check whether the query returns any rows.
If it doesn't pop up a message and set Cancel = True.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Richard" wrote in message
...
I have this query.


SELECT BOOKS.Number
FROM BOOKS
WHERE (((BOOKS.Number)=[Enter the number:]))
GROUP BY BOOKS.Number
ORDER BY BOOKS.Number DESC;


What I want to do now is if the query returns no results, display a
message
then go to Main Manu form.


Can this be done?


I am using Windows XP and Access 2000.


Many thanks in advance.
--
Richard


Dear Richard,

Let me give you the code what Sir-Douglas talking about.

Private Sub Form_Open(Cancel As Integer)

Dim Mydbs As Database
Dim Myrst As Recordset
Dim MyRecCount As Long
Set Mydbs = CurrentDb
With Mydbs
Set Myrst = .OpenRecordset(Form.RecordSource)
MyRecCount = Myrst.RecordCount

If MyRecCount = 0 Then
MsgBox "This BOOK Number has " & MyRecCount & " no
Record." _
& vbCrLf & "You do not need to view."
Cancel = True
Else

MsgBox "This BOOK Number has " & MyRecCount & "
Record(s)."
End If
End With
Set Mydbs = Nothing
Set Myrst = Nothing

End Sub
__________________________________________________ ________
Ko Zaw


  #6  
Old June 14th, 2007, 08:51 AM posted to microsoft.public.access.queries
Bamar
external usenet poster
 
Posts: 9
Default Return to form if query unmatched

On Jun 14, 1:19 pm, Richard wrote:
Thanks Ko Zaw

I have put your code into the Open event on my form, but I am getting the
following error message upon opening the form:

Compile Error:
User-define type not defined

and the following text is then highlighted:
Mydbs As Database

Any ideas?

Thank you for your help.
--
Richard

"Ko Zaw" wrote:
On Jun 14, 4:26 am, Richard wrote:
Thanks Doug


I have done some searching, but can't find out how to do what you are
suggesting. Can you help me further please.


Thank you.
--
Richard


"Douglas J. Steele" wrote:
In the form's Open event, you can check whether the query returns any rows.
If it doesn't pop up a message and set Cancel = True.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Richard" wrote in message
...
I have this query.


SELECT BOOKS.Number
FROM BOOKS
WHERE (((BOOKS.Number)=[Enter the number:]))
GROUP BY BOOKS.Number
ORDER BY BOOKS.Number DESC;


What I want to do now is if the query returns no results, display a
message
then go to Main Manu form.


Can this be done?


I am using Windows XP and Access 2000.


Many thanks in advance.
--
Richard


Dear Richard,


Let me give you the code what Sir-Douglas talking about.


Private Sub Form_Open(Cancel As Integer)


Dim Mydbs As Database
Dim Myrst As Recordset
Dim MyRecCount As Long
Set Mydbs = CurrentDb
With Mydbs
Set Myrst = .OpenRecordset(Form.RecordSource)
MyRecCount = Myrst.RecordCount


If MyRecCount = 0 Then
MsgBox "This BOOK Number has " & MyRecCount & " no
Record." _
& vbCrLf & "You do not need to view."
Cancel = True
Else


MsgBox "This BOOK Number has " & MyRecCount & "
Record(s)."
End If
End With
Set Mydbs = Nothing
Set Myrst = Nothing


End Sub
__________________________________________________ ________
Ko Zaw


Hi Richard,

That mean you have to add more reference.
These reference can add from Code Windows Tools Menu Reference
button.
Check Marks to following and compile it.
- Visual Basic For Applications
- Microsoft Access xx.x Object Library (xx.x refer to digit)
- Microsoft Office xx.x Access Database Engine Object Library

It is always best to compile database before running.
And it is a practice.
______________________________
Bamar NB: I've chaged My Nick Name from Ko Zaw to Bamar

  #7  
Old June 14th, 2007, 09:08 AM posted to microsoft.public.access.queries
Bamar
external usenet poster
 
Posts: 9
Default Return to form if query unmatched

On Jun 14, 1:51 pm, Bamar wrote:
On Jun 14, 1:19 pm, Richard wrote:



Thanks Ko Zaw


I have put your code into the Open event on my form, but I am getting the
following error message upon opening the form:


Compile Error:
User-define type not defined


and the following text is then highlighted:
Mydbs As Database


Any ideas?


Thank you for your help.
--
Richard


"Ko Zaw" wrote:
On Jun 14, 4:26 am, Richard wrote:
Thanks Doug


I have done some searching, but can't find out how to do what you are
suggesting. Can you help me further please.


Thank you.
--
Richard


"Douglas J. Steele" wrote:
In the form's Open event, you can check whether the query returns any rows.
If it doesn't pop up a message and set Cancel = True.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Richard" wrote in message
...
I have this query.


SELECT BOOKS.Number
FROM BOOKS
WHERE (((BOOKS.Number)=[Enter the number:]))
GROUP BY BOOKS.Number
ORDER BY BOOKS.Number DESC;


What I want to do now is if the query returns no results, display a
message
then go to Main Manu form.


Can this be done?


I am using Windows XP and Access 2000.


Many thanks in advance.
--
Richard


Dear Richard,


Let me give you the code what Sir-Douglas talking about.


Private Sub Form_Open(Cancel As Integer)


Dim Mydbs As Database
Dim Myrst As Recordset
Dim MyRecCount As Long
Set Mydbs = CurrentDb
With Mydbs
Set Myrst = .OpenRecordset(Form.RecordSource)
MyRecCount = Myrst.RecordCount


If MyRecCount = 0 Then
MsgBox "This BOOK Number has " & MyRecCount & " no
Record." _
& vbCrLf & "You do not need to view."
Cancel = True
Else


MsgBox "This BOOK Number has " & MyRecCount & "
Record(s)."
End If
End With
Set Mydbs = Nothing
Set Myrst = Nothing


End Sub
__________________________________________________ ________
Ko Zaw


Hi Richard,

That mean you have to add more reference.
These reference can add from Code Windows Tools Menu Reference
button.
Check Marks to following and compile it.
- Visual Basic For Applications
- Microsoft Access xx.x Object Library (xx.x refer to digit)
- Microsoft Office xx.x Access Database Engine Object Library

It is always best to compile database before running.
And it is a practice.
______________________________
Bamar NB: I've chaged My Nick Name from Ko Zaw to Bamar


Another Method. using Dcount Function, which is simpler.

Private Sub Form_Open(Cancel As Integer)

Dim MyRecCount As Long
MyRecCount = DCount("*", Form.RecordSource)

If MyRecCount = 0 Then
MsgBox "This BOOK Number has " & MyRecCount & " no
Record." _
& vbCrLf & "You do not need to view."
Cancel = True
Else

MsgBox "This BOOK Number has " & MyRecCount & "
Record(s)."
End If
End Sub

__________________________________
Bamar

  #8  
Old June 14th, 2007, 10:03 AM posted to microsoft.public.access.queries
Richard
external usenet poster
 
Posts: 1,419
Default Return to form if query unmatched

Thanks Bamar

I am trying to use your simpler version.

I am getting the following error message:

Run-time error ‘2471’:

The expression you entered as a query parameter produced this error:
‘The object doesn’t contain the Automation object ‘Enter the book number:.”

Sorry to be a pain, but any idea what could be causing this?

I am by no means a programmer and need a lot of help with the coding.

Thanks again for your help.
--
Richard


"Bamar" wrote:

On Jun 14, 1:51 pm, Bamar wrote:
On Jun 14, 1:19 pm, Richard wrote:



Thanks Ko Zaw


I have put your code into the Open event on my form, but I am getting the
following error message upon opening the form:


Compile Error:
User-define type not defined


and the following text is then highlighted:
Mydbs As Database


Any ideas?


Thank you for your help.
--
Richard


"Ko Zaw" wrote:
On Jun 14, 4:26 am, Richard wrote:
Thanks Doug


I have done some searching, but can't find out how to do what you are
suggesting. Can you help me further please.


Thank you.
--
Richard


"Douglas J. Steele" wrote:
In the form's Open event, you can check whether the query returns any rows.
If it doesn't pop up a message and set Cancel = True.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Richard" wrote in message
...
I have this query.


SELECT BOOKS.Number
FROM BOOKS
WHERE (((BOOKS.Number)=[Enter the number:]))
GROUP BY BOOKS.Number
ORDER BY BOOKS.Number DESC;


What I want to do now is if the query returns no results, display a
message
then go to Main Manu form.


Can this be done?


I am using Windows XP and Access 2000.


Many thanks in advance.
--
Richard


Dear Richard,


Let me give you the code what Sir-Douglas talking about.


Private Sub Form_Open(Cancel As Integer)


Dim Mydbs As Database
Dim Myrst As Recordset
Dim MyRecCount As Long
Set Mydbs = CurrentDb
With Mydbs
Set Myrst = .OpenRecordset(Form.RecordSource)
MyRecCount = Myrst.RecordCount


If MyRecCount = 0 Then
MsgBox "This BOOK Number has " & MyRecCount & " no
Record." _
& vbCrLf & "You do not need to view."
Cancel = True
Else


MsgBox "This BOOK Number has " & MyRecCount & "
Record(s)."
End If
End With
Set Mydbs = Nothing
Set Myrst = Nothing


End Sub
__________________________________________________ ________
Ko Zaw


Hi Richard,

That mean you have to add more reference.
These reference can add from Code Windows Tools Menu Reference
button.
Check Marks to following and compile it.
- Visual Basic For Applications
- Microsoft Access xx.x Object Library (xx.x refer to digit)
- Microsoft Office xx.x Access Database Engine Object Library

It is always best to compile database before running.
And it is a practice.
______________________________
Bamar NB: I've chaged My Nick Name from Ko Zaw to Bamar


Another Method. using Dcount Function, which is simpler.

Private Sub Form_Open(Cancel As Integer)

Dim MyRecCount As Long
MyRecCount = DCount("*", Form.RecordSource)

If MyRecCount = 0 Then
MsgBox "This BOOK Number has " & MyRecCount & " no
Record." _
& vbCrLf & "You do not need to view."
Cancel = True
Else

MsgBox "This BOOK Number has " & MyRecCount & "
Record(s)."
End If
End Sub

__________________________________
Bamar


  #9  
Old June 14th, 2007, 11:34 AM posted to microsoft.public.access.queries
Bamar
external usenet poster
 
Posts: 9
Default Return to form if query unmatched

On Jun 14, 3:03 pm, Richard wrote:
Thanks Bamar

I am trying to use your simpler version.

I am getting the following error message:

Run-time error '2471':

The expression you entered as a query parameter produced this error:
'The object doesn't contain the Automation object 'Enter the book number:."

Sorry to be a pain, but any idea what could be causing this?

I am by no means a programmer and need a lot of help with the coding.

Thanks again for your help.
--
Richard

"Bamar" wrote:
On Jun 14, 1:51 pm, Bamar wrote:
On Jun 14, 1:19 pm, Richard wrote:


Thanks Ko Zaw


I have put your code into the Open event on my form, but I am getting the
following error message upon opening the form:


Compile Error:
User-define type not defined


and the following text is then highlighted:
Mydbs As Database


Any ideas?


Thank you for your help.
--
Richard


"Ko Zaw" wrote:
On Jun 14, 4:26 am, Richard wrote:
Thanks Doug


I have done some searching, but can't find out how to do what you are
suggesting. Can you help me further please.


Thank you.
--
Richard


"Douglas J. Steele" wrote:
In the form's Open event, you can check whether the query returns any rows.
If it doesn't pop up a message and set Cancel = True.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Richard" wrote in message
...
I have this query.


SELECT BOOKS.Number
FROM BOOKS
WHERE (((BOOKS.Number)=[Enter the number:]))
GROUP BY BOOKS.Number
ORDER BY BOOKS.Number DESC;


What I want to do now is if the query returns no results, display a
message
then go to Main Manu form.


Can this be done?


I am using Windows XP and Access 2000.


Many thanks in advance.
--
Richard


Dear Richard,


Let me give you the code what Sir-Douglas talking about.


Private Sub Form_Open(Cancel As Integer)


Dim Mydbs As Database
Dim Myrst As Recordset
Dim MyRecCount As Long
Set Mydbs = CurrentDb
With Mydbs
Set Myrst = .OpenRecordset(Form.RecordSource)
MyRecCount = Myrst.RecordCount


If MyRecCount = 0 Then
MsgBox "This BOOK Number has " & MyRecCount & " no
Record." _
& vbCrLf & "You do not need to view."
Cancel = True
Else


MsgBox "This BOOK Number has " & MyRecCount & "
Record(s)."
End If
End With
Set Mydbs = Nothing
Set Myrst = Nothing


End Sub
__________________________________________________ ________
Ko Zaw


Hi Richard,


That mean you have to add more reference.
These reference can add from Code Windows Tools Menu Reference
button.
Check Marks to following and compile it.
- Visual Basic For Applications
- Microsoft Access xx.x Object Library (xx.x refer to digit)
- Microsoft Office xx.x Access Database Engine Object Library


It is always best to compile database before running.
And it is a practice.
______________________________
Bamar NB: I've chaged My Nick Name from Ko Zaw to Bamar


Another Method. using Dcount Function, which is simpler.


Private Sub Form_Open(Cancel As Integer)


Dim MyRecCount As Long
MyRecCount = DCount("*", Form.RecordSource)


If MyRecCount = 0 Then
MsgBox "This BOOK Number has " & MyRecCount & " no
Record." _
& vbCrLf & "You do not need to view."
Cancel = True
Else


MsgBox "This BOOK Number has " & MyRecCount & "
Record(s)."
End If
End Sub


__________________________________
Bamar


Dear Richard,

Please review your 1st post.

Your sql statement already has this parameter asking to enter it. So
it was asking it value.
This problem is not concern with any Form Open code, this is about
record source of that form.

Try create a new query, turn in to Design View and then to sql view,
and paste your sql in your 1st post.
And just turn into Datasheet view. If there is an error, it can be
clearly seen that your sql has error.
Try amend it until no errror and then use as record source of that
form.

(OR)
You can let your Form to ask that Book Number, and it no Book Number
is provided, then let that Form to close it-self.
Which may be as following.

Private Sub Form_Open(Cancel As Integer)
Dim Str As String
Dim MyBookNumber As Long
Dim L
Dim i As Integer
i = 1
Do
L = InputBox("What is your Book Number?", , 1)
i = i + 1
If i = 10 Then
MsgBox "You cancel Ten Times. Will Exit!"
Cancel = True
Exit Sub
End If
Loop While L = ""
MyBookNumber = CLng(L)

Str = "SELECT BOOKS.Number " _
& "FROM BOOKS " _
& "WHERE (((BOOKS.Number) = " _
& MyBookNumber & " )) " _
& "GROUP BY BOOKS.Number " _
& "ORDER BY BOOKS.Number DESC"

Form.RecordSource = Str
' Now [Enter the number:] became MyBookNumber

Dim MyRecCount As Long
' Form.RecordsetClone.MoveLast
MyRecCount = Form.RecordsetClone.RecordCount

If MyRecCount = 0 Then
MsgBox "This BOOK Number has " & MyRecCount & " no
Record." _
& vbCrLf & "You do not need to view."
Cancel = True
Else

MsgBox "This BOOK Number has " & MyRecCount & "
Record(s)."
End If

End Sub
__________________________________________________
Brgds,
Bamar

  #10  
Old June 14th, 2007, 12:52 PM posted to microsoft.public.access.queries
Richard
external usenet poster
 
Posts: 1,419
Default Return to form if query unmatched

Thanks Bamar

The final solution I have gone with is using my original query as datasource
for the form and use the following code in the mouse move event of the form.

Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single,
Y As Single)
On Error GoTo MouseMove_Err
Dim MyRecCount As Long
MyRecCount = DCount("*", Form.RecordSource)

MouseMove_Exit:
Exit Sub

MouseMove_Err:
MsgBox "No record was found"
DoCmd.RunCommand acCmdClose
Resume MouseMove_Exit
End Sub

So if the query returns greater than 0 the user is taken to the correct
record on the form.

If the query returns 0 then the mouse move event automatically kicks in and
the form displays a message, which when clicked closes the form, and the user
is taken back to the Main Menu.

Thanks to everyone who helped.
--
Richard


"Bamar" wrote:

On Jun 14, 3:03 pm, Richard wrote:
Thanks Bamar

I am trying to use your simpler version.

I am getting the following error message:

Run-time error '2471':

The expression you entered as a query parameter produced this error:
'The object doesn't contain the Automation object 'Enter the book number:."

Sorry to be a pain, but any idea what could be causing this?

I am by no means a programmer and need a lot of help with the coding.

Thanks again for your help.
--
Richard

"Bamar" wrote:
On Jun 14, 1:51 pm, Bamar wrote:
On Jun 14, 1:19 pm, Richard wrote:


Thanks Ko Zaw


I have put your code into the Open event on my form, but I am getting the
following error message upon opening the form:


Compile Error:
User-define type not defined


and the following text is then highlighted:
Mydbs As Database


Any ideas?


Thank you for your help.
--
Richard


"Ko Zaw" wrote:
On Jun 14, 4:26 am, Richard wrote:
Thanks Doug


I have done some searching, but can't find out how to do what you are
suggesting. Can you help me further please.


Thank you.
--
Richard


"Douglas J. Steele" wrote:
In the form's Open event, you can check whether the query returns any rows.
If it doesn't pop up a message and set Cancel = True.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Richard" wrote in message
...
I have this query.


SELECT BOOKS.Number
FROM BOOKS
WHERE (((BOOKS.Number)=[Enter the number:]))
GROUP BY BOOKS.Number
ORDER BY BOOKS.Number DESC;


What I want to do now is if the query returns no results, display a
message
then go to Main Manu form.


Can this be done?


I am using Windows XP and Access 2000.


Many thanks in advance.
--
Richard


Dear Richard,


Let me give you the code what Sir-Douglas talking about.


Private Sub Form_Open(Cancel As Integer)


Dim Mydbs As Database
Dim Myrst As Recordset
Dim MyRecCount As Long
Set Mydbs = CurrentDb
With Mydbs
Set Myrst = .OpenRecordset(Form.RecordSource)
MyRecCount = Myrst.RecordCount


If MyRecCount = 0 Then
MsgBox "This BOOK Number has " & MyRecCount & " no
Record." _
& vbCrLf & "You do not need to view."
Cancel = True
Else


MsgBox "This BOOK Number has " & MyRecCount & "
Record(s)."
End If
End With
Set Mydbs = Nothing
Set Myrst = Nothing


End Sub
__________________________________________________ ________
Ko Zaw


Hi Richard,


That mean you have to add more reference.
These reference can add from Code Windows Tools Menu Reference
button.
Check Marks to following and compile it.
- Visual Basic For Applications
- Microsoft Access xx.x Object Library (xx.x refer to digit)
- Microsoft Office xx.x Access Database Engine Object Library


It is always best to compile database before running.
And it is a practice.
______________________________
Bamar NB: I've chaged My Nick Name from Ko Zaw to Bamar


Another Method. using Dcount Function, which is simpler.


Private Sub Form_Open(Cancel As Integer)


Dim MyRecCount As Long
MyRecCount = DCount("*", Form.RecordSource)


If MyRecCount = 0 Then
MsgBox "This BOOK Number has " & MyRecCount & " no
Record." _
& vbCrLf & "You do not need to view."
Cancel = True
Else


MsgBox "This BOOK Number has " & MyRecCount & "
Record(s)."
End If
End Sub


__________________________________
Bamar


Dear Richard,

Please review your 1st post.

Your sql statement already has this parameter asking to enter it. So
it was asking it value.
This problem is not concern with any Form Open code, this is about
record source of that form.

Try create a new query, turn in to Design View and then to sql view,
and paste your sql in your 1st post.
And just turn into Datasheet view. If there is an error, it can be
clearly seen that your sql has error.
Try amend it until no errror and then use as record source of that
form.

(OR)
You can let your Form to ask that Book Number, and it no Book Number
is provided, then let that Form to close it-self.
Which may be as following.

Private Sub Form_Open(Cancel As Integer)
Dim Str As String
Dim MyBookNumber As Long
Dim L
Dim i As Integer
i = 1
Do
L = InputBox("What is your Book Number?", , 1)
i = i + 1
If i = 10 Then
MsgBox "You cancel Ten Times. Will Exit!"
Cancel = True
Exit Sub
End If
Loop While L = ""
MyBookNumber = CLng(L)

Str = "SELECT BOOKS.Number " _
& "FROM BOOKS " _
& "WHERE (((BOOKS.Number) = " _
& MyBookNumber & " )) " _
& "GROUP BY BOOKS.Number " _
& "ORDER BY BOOKS.Number DESC"

Form.RecordSource = Str
' Now [Enter the number:] became MyBookNumber

Dim MyRecCount As Long
' Form.RecordsetClone.MoveLast
MyRecCount = Form.RecordsetClone.RecordCount

If MyRecCount = 0 Then
MsgBox "This BOOK Number has " & MyRecCount & " no
Record." _
& vbCrLf & "You do not need to view."
Cancel = True
Else

MsgBox "This BOOK Number has " & MyRecCount & "
Record(s)."
End If

End Sub
__________________________________________________
Brgds,
Bamar


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:31 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.