Re: Return to form if query unmatched

Tech-Archive recommends: Speed Up your PC by fixing your registry

Hi! Richard,

I'm glad now you have the right way.
Mouse move event is not for no-record error handling.

______________________________________
Kozaw


On Jun 18, 11:50 am, Richard <Rich...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
Thanks Kosaw

I have removed all the Group by's and I can now edit records after running
the query.

I posted my question in the Form Coding group and found another solution
that works.

I have created a form/parameter box where I enter the number, this then
sends the number to he main BOOKS form, which is based on the following query:

SELECT [BOOKS].[Number], [BOOKS].[Number 2], [BOOKS].[BookDate],
[BOOKS].[Forename], [BOOKS].[Surname], [BOOKS].[Unit], [BOOKS].[Returned],
[BOOKS].[Archived]
FROM BOOKS
WHERE
((([BOOKS].[Number])=Left([Forms]![Prompt_Books_query]![enter_number],5) &
IIf(Val(Right([Forms]![Prompt_Books_query]![enter_number],2)>50),"51","01")))
ORDER BY [BOOKS].[Number] DESC;

I have removed the coding from on mouse move.

Thank you for your help.
--
Richard

"Kozaw" wrote:
Dear Richard,

So sorry for being silent.
Your query (Form's Record Source) return read-only list, that user can
view which books in the list are available.
Being grouping list, this can't be edit, that's why you can't amend
it.

Please tell me, why are you grouping the fields so much. If you just
need sorting and want to amend the reocrds, then Form's Record Source
should be editable Table/Query, and therefore you must discard
grouping.

____________________________
Kozaw

On Jun 15, 8:24 pm, Richard <Rich...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Ok, I think I have a simple work around. I have replaced the mouse move vb
code with a simple macro which closes the form on mouse move. Then I have
placed a rectangle object to cover the whole form. So if the query returns
nothing the form is blank and has no rectangle and so the mouse move closes
the form.

However, I now need to be able to amend records if the query returns
records. It is not letting me amend records. What do I need to do.

Thanks again.
--
Richard

"Kozaw" wrote:
On Jun 15, 11:45 am, Richard <Rich...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
Thanks Bamar

You are right, it is bringing up the same error message. However, I have
found something new. The query only fails if the user enters a 0 into the
parameter box.

So how can I alter the code to say if the user enters a 0 then close form?

Again many thanks with this.
--
Richard

"Bamar" wrote:
On Jun 14, 5:52 pm, Richard <Rich...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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 <Rich...@xxxxxxxxxxxxxxxxxxxxxxxxx> 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 <ko.zawminl...@xxxxxxxxx> wrote:
On Jun 14, 1:19 pm, Richard <Rich...@xxxxxxxxxxxxxxxxxxxxxxxxx> 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 <Rich...@xxxxxxxxxxxxxxxxxxxxxxxxx> 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" <Rich...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FEE65035-EA51-4F70-AF3D-E795EC93C6F5@xxxxxxxxxxxxxxxx
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

...

read more »


.

Quantcast