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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Return to form if query unmatched
On Jun 14, 5:52 pm, Richard 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 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 Dear Richard, I'm not sure your coding is correct. Please test after commenting/deleting following two lines in your mouse_move event. These tow line done nothing in your code. Dim MyRecCount As Long MyRecCount = DCount("*", Form.RecordSource) I think your Recordsource query already have required parameter, so It can open it and gone up to current state. Then, you just triggered error on mouse_move event. That mean if any error (regardless of record count) is triggered on mouse move, form will close. If you want to know what error is occurred, just replace your error handling message as follow. MsgBox Err.Number & Err.Description Then, you will see what error is. It may not be zero record error. I writing to you with all my code after simulating on my end. __________________________ Bamar |
#12
|
|||
|
|||
Return to form if query unmatched
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 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 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 Dear Richard, I'm not sure your coding is correct. Please test after commenting/deleting following two lines in your mouse_move event. These tow line done nothing in your code. Dim MyRecCount As Long MyRecCount = DCount("*", Form.RecordSource) I think your Recordsource query already have required parameter, so It can open it and gone up to current state. Then, you just triggered error on mouse_move event. That mean if any error (regardless of record count) is triggered on mouse move, form will close. If you want to know what error is occurred, just replace your error handling message as follow. MsgBox Err.Number & Err.Description Then, you will see what error is. It may not be zero record error. I writing to you with all my code after simulating on my end. __________________________ Bamar |
#13
|
|||
|
|||
Return to form if query unmatched
On Jun 15, 11:45 am, Richard
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 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 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 Dear Richard, I'm not sure your coding is correct. Please test after commenting/deleting following two lines in your mouse_move event. These tow line done nothing in your code. Dim MyRecCount As Long MyRecCount = DCount("*", Form.RecordSource) I think your Recordsource query already have required parameter, so It can open it and gone up to current state. Then, you just triggered error on mouse_move event. That mean if any error (regardless of record count) is triggered on mouse move, form will close. If you want to know what error is occurred, just replace your error handling message as follow. MsgBox Err.Number & Err.Description Then, you will see what error is. It may not be zero record error. I writing to you with all my code after simulating on my end. __________________________ Bamar Yes. That's what I'm talking about. I tested. if user enter 0, error will triggered. If user enter any numbers other than your book.number, error will also triggered. Please test it. 1st let me know your form's record source value. If it is a sql then you should create a query with that sql, and then make record source as that query name. by doing this your parameter [Enter the number:] will have exact value/ type for searching. NB: Actually you should have created a combo box on your main form, and set it's row source to the concise list of book number. When user select book number from that box and hit button, your form will show exactly what they want to view. So, no error can occur. It's never late, consider again. _________________________ Rgds, |
#14
|
|||
|
|||
Return to form if query unmatched
Thanks Kozaw.
This is now what I am working with, as follows: The table: There 9 fields, I need to display 8 of them on the form. The query looks at the 'Number' field, however the 'Number' field is specified as 'Text' in the design table. The query: SELECT [BOOKS].[Number], [BOOKS].[Number 2], [BOOKS].[Date], [BOOKS].[Forename], [BOOKS].[Surname], [BOOKS].[Unit], [BOOKS].[Returned], [BOOKS].[Archived] FROM BOOKS WHERE ((([BOOKS].[Number])=[Please enter a 7 digit sheet number:])) GROUP BY [BOOKS].[Number], [BOOKS].[Number 2], [BOOKS].[Date], [BOOKS].[Forename], [BOOKS].[Surname], [BOOKS].[Unit], [BOOKS].[Returned], [BOOKS].[Archived] ORDER BY [BOOKS].[Number] DESC; The form: The forms record source is the query above. The form has 8 fields. The mouse over code: Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single) 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 I am no getting the following error message using the above: Run-time error ‘3061’: Too few parameters. Expected 1. Any ideas anyone? Thanks again. -- Richard "Kozaw" wrote: On Jun 15, 11:45 am, Richard 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 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 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 Dear Richard, I'm not sure your coding is correct. Please test after commenting/deleting following two lines in your mouse_move event. These tow line done nothing in your code. Dim MyRecCount As Long MyRecCount = DCount("*", Form.RecordSource) I think your Recordsource query already have required parameter, so It can open it and gone up to current state. Then, you just triggered error on mouse_move event. That mean if any error (regardless of record count) is triggered on mouse move, form will close. If you want to know what error is occurred, just replace your error handling message as follow. MsgBox Err.Number & Err.Description Then, you will see what error is. It may not be zero record error. I writing to you with all my code after simulating on my end. __________________________ Bamar Yes. That's what I'm talking about. I tested. if user enter 0, error will triggered. If user enter any numbers other than your book.number, error will also triggered. Please test it. 1st let me know your form's record source value. |
#15
|
|||
|
|||
Return to form if query unmatched
The query works fine by itself, it's when I open the form using the mouse
over vb code that seems to be the problem. -- Richard "Kozaw" wrote: On Jun 15, 11:45 am, Richard 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 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 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 Dear Richard, I'm not sure your coding is correct. Please test after commenting/deleting following two lines in your mouse_move event. These tow line done nothing in your code. Dim MyRecCount As Long MyRecCount = DCount("*", Form.RecordSource) I think your Recordsource query already have required parameter, so It can open it and gone up to current state. Then, you just triggered error on mouse_move event. That mean if any error (regardless of record count) is triggered on mouse move, form will close. If you want to know what error is occurred, just replace your error handling message as follow. MsgBox Err.Number & Err.Description Then, you will see what error is. It may not be zero record error. I writing to you with all my code after simulating on my end. __________________________ Bamar Yes. That's what I'm talking about. I tested. if user enter 0, error will triggered. If user enter any numbers other than your book.number, error will also triggered. Please test it. 1st let me know your form's record source value. |
#16
|
|||
|
|||
Return to form if query unmatched
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 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 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 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 Dear Richard, I'm not sure your coding is correct. Please test after commenting/deleting following two lines in your mouse_move event. These tow line done nothing in your code. Dim MyRecCount As Long MyRecCount = DCount("*", Form.RecordSource) I think your Recordsource query already have required parameter, so It can open it and gone up to current state. Then, you just triggered error on mouse_move event. That mean if any error (regardless of record count) is triggered on mouse move, form will close. If you want to know what error is occurred, just replace your error handling message as follow. MsgBox Err.Number & Err.Description Then, you will see what error is. It may not be zero record error. I writing to you with all my code after simulating on my end. __________________________ Bamar Yes. That's what I'm talking about. I tested. if user enter 0, error will triggered. If user enter any numbers other than your book.number, error will also triggered. Please test it. 1st let me know your form's record source value. |
#17
|
|||
|
|||
Return to form if query unmatched
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 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 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 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 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." _ ... read more » |
#18
|
|||
|
|||
Return to form if query unmatched
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 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 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 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 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." _ ... read more ; |
#19
|
|||
|
|||
Return to form if query unmatched
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 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 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 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 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 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 ... read more » |
|
Thread Tools | |
Display Modes | |
|
|