[Form] Can I press a button to run 2 queries? (1 Viewer)

kekeke · Nov 15, 2018

Hi all

I am trying to do my final year project on a visitor management system with Microsoft Access.

Now I have check in page where i want to click a button to:
1) run valid query
[if yes, then open form on valid visitor. if NO, open form on invalid visitor]

Is it possible to do the above in Microsoft Access? Any advice is appreciated!

Thanks in advance!

Signing off....
kekeke

Ranman256 · Nov 15, 2018

Code:

sub btnGo_click()
docmd.openquery "query1"
docmd.openquery "query2"
end sub

or

Code:

sub btnGo_click()
if Dcount("*", "qsValidate") = 0 then   'no errors
   docmd.openquery "query2"
endif
end sub

Cliff67 · Nov 15, 2018

Hello
How's your VBA skills. You can open a recordset and check if the visitor name is in the table. Try somnething like the code below

'lets open the form find and add the data from my form
'check if the visitor exists
Set db = CurrentDb
Set Rs = db.OpenRecordset("MyTableName")
Rs.MoveFirst
If Not Rs.BOF And Not Rs.EOF Then
Do Until Rs.EOF = True
If Rs!VisitorName = Me.ControlName Then
MsgBox "This Visitor number already exists!", vbInformation, "Find Visitor"
DoCmd.OpenForm "MyFormName ", acNormal, "", "[VisitorName]=" & "'" & Me.ControlName & "'", acFormEdit
Exit Sub
Else
Rs.MoveNext
End If
Loop
End If
‘not found open new form and add visitor name
DoCmd.OpenForm "MyFormName", acNormal, , , acFormAdd
Set myfrm = Forms("MyFormName")
myfrm!VisitorName = Me.ControlName
‘do the same for any other data you want to add

replace the generic terms with the ones for your database
hope it helps
Cliff

kekeke · Nov 15, 2018

Cliff67 said:
Hello
How's your VBA skills. You can open a recordset and check if the visitor name is in the table. Try somnething like the code below

'lets open the form find and add the data from my form
'check if the visitor exists
Set db = CurrentDb
Set Rs = db.OpenRecordset("MyTableName")
Rs.MoveFirst
If Not Rs.BOF And Not Rs.EOF Then
Do Until Rs.EOF = True
If Rs!VisitorName = Me.ControlName Then
MsgBox "This Visitor number already exists!", vbInformation, "Find Visitor"
DoCmd.OpenForm "MyFormName ", acNormal, "", "[VisitorName]=" & "'" & Me.ControlName & "'", acFormEdit
Exit Sub
Else
Rs.MoveNext
End If
Loop
End If
‘not found open new form and add visitor name
DoCmd.OpenForm "MyFormName", acNormal, , , acFormAdd
Set myfrm = Forms("MyFormName")
myfrm!VisitorName = Me.ControlName
‘do the same for any other data you want to add

replace the generic terms with the ones for your database
hope it helps
Cliff

Hi

I do not have any knowledge on VBA

But thanks for the code.. I will look through and understand it. And try it inside my Microsoft Access.

Cliff67 · Nov 15, 2018

Which version are you using?

kekeke · Nov 15, 2018

Ranman256 said:

Code:

sub btnGo_click()
docmd.openquery "query1"
docmd.openquery "query2"
end sub

or

Code:

sub btnGo_click()
if Dcount("*", "qsValidate") = 0 then   'no errors
   docmd.openquery "query2"
endif
end sub

Hi Ranman256

I tried both codes (replace with relevant query names with mine) but they prompt me the below error.

there is an error occurred while Microsoft Access was communicating with OLE server or ActiveX Control.
*The expression may not result in the name of a macro, the name of a user-defined function, or [event procedure].
*There may have been an error evaluating the function, event, or macro.

kekeke · Nov 15, 2018

Cliff67 said:
Which version are you using?

Hi Cliff67

I am using Microsoft Access 2016.

Cliff67 · Nov 15, 2018

If you are using A2013 then there is a very good youtube tutorial done by a guy call Steve Bishop it's called Programming in Microsoft Access 2013 and VBA. He gives real world examples as he has been going through major developments. I can't post the link

I would recommend you have a look at it

Cliff67 · Nov 15, 2018

Everything he does in 2013 should apply to 2016 as well

Cliff67 · Nov 15, 2018

In that code you need to replace EndIf with End If.

Don't forget to compile your code as you go and use Option Explicit at the beginning of any form code or modules you write.

This tells access that you must explicitly declare all variables by using the Dim or ReDim statements

Cliff67 · Nov 15, 2018

Here you go I can do the link now

https://www.youtube.com/playlist?list=PLYMOUCVo86jEeMMdaaq03jQ_t9nFV737s

Cliff

MajP · Nov 15, 2018

Cliff,
You should almost never open a recordset with all the records and loop it to see if something exists. You can use a recordset if you wanted, but you should open only what you are looking for in this case a single record. Both easier to code and way faster if you have lots of records

Code:

''check if the visitor exists
  Set db = CurrentDb  
  Set Rs = db.OpenRecordset("select VisitorName from myTable where visitorname = '" & me.controlName & "'")

If Rs.BOF And Rs.EOF Then
  ‘not found open new form and add visitor name
   'code here  
Else
  'found code here
End If

Cliff67 · Nov 15, 2018

Hi MajP

yes you are correct in what you are saying, this was meant as a rough and ready example for a small scale db to get the user started.

I use used this approach only on small tables of say less than 100 records anything bigger gives a performance hit on a server

thanks for the feed back

kekeke · Nov 15, 2018

May i just ask why does the following error keep popping up when i am exploring the codes you all have provided? I replaced with the respective names of forms/queries but this error still pop up.

Sorry as I do not know how to code VBA so is trying my best to understand it line by line.

""there is an error occurred while Microsoft Access was communicating with OLE server or ActiveX Control.
*The expression may not result in the name of a macro, the name of a user-defined function, or [event procedure].
*There may have been an error evaluating the function, event, or macro.""

Thanks all~~

Cliff67 · Nov 15, 2018

put at the top

On error goto ErrHand ' this turns the error handling on

Code is here

then at the bottom
on error goto 0 'this turns the error handling off
exit sub

ErrHand: 'this is the error handler and will give you an error code and description of the error
MsgBox Err.Number &" " & Err.Description
err.clear
exit sub

then run the code again then let me know what the error number and description is

Ranman256 · Nov 15, 2018

these commands do not use OLE, nor ActiveX controls.

Cliff67 · Nov 16, 2018

Yes, the only thing I can think is that there is a missing library link

kekeke · Nov 20, 2018

Hi All

Thanks for your inputs.. I have trouble using VBA so i explored possibility of macros instead.

I was able to use macro to trigger from clicking a single button to run 2 queries. My queries are already link to respective forms. Thus, I have 2 forms:
1) Valid Visitor
2) Invalid Visitor

I used If else statement in macro.

If [ID] = [Database].[ID No]
Open Form of Valid Visitor

Else Open Form of Invalid Visitor

End If

However, I get the following error:
A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control. Close the OLE server and restart it outside of Microsoft Access. Then try the original operation again in Microsoft Access

Any idea why?

Thanks all!

Cliff67 · Nov 20, 2018

You have a missing library reference Launch your VBA window then from the tools menu select references . Look for the Mircrosoft Office 15.0 Object Library and select it. Then compile your code and that should go away

Good luck

kekeke · Nov 20, 2018

Cliff67 said:
You have a missing library reference Launch your VBA window then from the tools menu select references . Look for the Mircrosoft Office 15.0 Object Library and select it. Then compile your code and that should go away

Good luck

Dear Cliff67

This library was selected in the references. I compiled it then run view in form, I was prompted the same error with also error code 2753. I am not so sure what is wrong with the OLE server.

I tried researching online for solutions but could not get any.

[Form] Can I press a button to run 2 queries? (1 Viewer)

New member

Well-known member

Registered User.

New member

Registered User.

New member

New member

Registered User.

Registered User.

Registered User.

Registered User.

You've got your good things, and you've got mine.

Registered User.

New member

Registered User.

Well-known member

Registered User.

New member

Registered User.

New member

Similar threads

Users who are viewing this thread