[SOLVED]Access2Base Macro

11 replies [Last post]
Thu, 17 Mar 2016 - 7:10 AM
Nixonfamilyusa
Offline
Last seen: 7 weeks 5 days ago
Title: ★
Joined: 17 Mar 2016
Posts: 6
See the post below for context. I solved this issue by rearranging the macro order. I inserted the OpenForm macro inside the DBOpen and DBClose macros, figuring that the connection to the database was closing before i was able to run the open form macro. Final code looks like this: 

Sub DBOpen(Optional poEvent As Object)
	If GlobalScope.BasicLibraries.hasByName("Access2Base") then
		GlobalScope.BasicLibraries.loadLibrary("Access2Base")
	End If
	Call Application.OpenConnection(ThisDatabaseDocument)
End Sub

Sub Main 
	DoCmd.OpenForm("Strength")
End sub

Sub DBClose(Optional poEvent As Object)
	Call CloseConnection()
End Sub

=====================================================================================================
Hopefully this is the right forum for this post, if not please move it as appropriate.

I am using Base after having worked in in Access.

I am wanting a simple Macro to open a form by clicking a button.
I followed the Access2Base documentation and entered this snippet initially:
Sub DBOpen(Optional poEvent As Object) If GlobalScope.BasicLibraries.hasByName(“Access2Base”) then GlobalScope.BasicLibraries.loadLibrary(“Access2Base”) End If Call Application.OpenConnection(ThisDatabaseDocument)
End Sub

Sub DBClose(Optional poEvent As Object) Call CloseConnection()
End Sub

then i added this macro to open a form named Strength:
Sub Macro1 DoCmd.OpenForm “Strength”, acNormal, “”, “”, , acNormal
End sub*

On pressing F5 i get an “object variable not set error”.
If i remove the DoCmd. i get a “Sub-procedure or function procedure not defined” error

any ideas or suggestions would be greatly appreciated.
thanks,
shaun

Top
Thu, 17 Mar 2016 - 8:27 AM
#1
echo8hink
Offline
Last seen: 7 weeks 4 days ago
Title: ★★★
Joined: 10 Mar 2016
Posts: 21
Try verifying the library load
The Macro1 code you list works for me when Access2Base is loaded. Of course errors will occur when it does not load.

Try putting the following extra line in the code where you load the library:


GlobalScope.BasicLibraries.loadLibrary("Access2Base")
MsgBox("Access2Base Lib loaded...")
Call Application.OpenConnection(ThisDatabaseDocument)

The message box will let you better know if the library is loaded when you open your LO base document.

-Dave
Top
Thu, 17 Mar 2016 - 8:45 AM
#2
Nixonfamilyusa
Offline
Last seen: 7 weeks 5 days ago
Title: ★
Joined: 17 Mar 2016
Posts: 6
thanks Dave
I added the msgbox as you suggested, It works great to show that the library is loaded when running that specific macro.

Appreciate the help. This is what my final code looks like:


Sub DBOpen(Optional poEvent As Object)
	If GlobalScope.BasicLibraries.hasByName("Access2Base") then
		GlobalScope.BasicLibraries.loadLibrary("Access2Base")
		MsgBox("Access2Base Lib loaded...")
	End If
	Call Application.OpenConnection(ThisDatabaseDocument)
End Sub

Sub Main 
	DoCmd.OpenForm("Strength")
End sub

Sub DBClose(Optional poEvent As Object)
	Call CloseConnection()
End Sub

Top
Thu, 17 Mar 2016 - 10:30 AM
#3
echo8hink
Offline
Last seen: 7 weeks 4 days ago
Title: ★★★
Joined: 10 Mar 2016
Posts: 21
checking for library loaded
A better way to check the if the Access2Base library is loaded would probably be: 

Sub IsAccess2BaseLoaded()
	If GlobalScope.BasicLibraries.isLibraryLoaded("Access2Base") Then
		MsgBox("Access2Base is loaded.")
	  Else 
	  	MsgBox("Access2Base is NOT loaded.")
	End If
End Sub

Glad to see you got it going…

-Dave
Top
Thu, 17 Mar 2016 - 11:40 AM
#4
Nixonfamilyusa
Offline
Last seen: 7 weeks 5 days ago
Title: ★
Joined: 17 Mar 2016
Posts: 6
macro still acts buggy
Thanks, I have noticed the macro does work, however, it only works if you run the DBOpen macro first. If i run that, then try to run the OpenForm macro, i have no problems but if i fail to run the DBOpen first then the openform will not work.

Still trying to rap my mind around this, being a LO newbie.

I am about to try your example and see if that fixes things.
shaun

Top
Thu, 17 Mar 2016 - 1:11 PM
#5
echo8hink
Offline
Last seen: 7 weeks 4 days ago
Title: ★★★
Joined: 10 Mar 2016
Posts: 21
checking for library loaded
The authors of Access2Base suggest to tie the DBOpen macro to the database Open Document event.

I set up a new Basic Module and add the open and close macros in the database document so they will always go with the odb where they are needed. You can try other setups as well. There is a message here in the forum with a collection of links to Base and macro documentation. If I can find it I may come back here and link it. documentation link

You need to go to Base menus, Tools->Customize and Events Tab. There you click on the “Open Document” and click “Macro” to assign the DBOpen macro. It will then load the library whenever you open the database. In that way, the library may be used at will while the database is in use. The DBClose event is to go away clean when you are done in the database. They suggest executing it on the “View is going to be closed” event.

You will have to save, close and reopen the database for the action of “Open Document” to run.

I use a different macro to open a form when my database opens so I added the DBOpen code to it so both would execute on the Open Document event. Then I can use Access2Base library in my other macros as long as the database is running.

Does that make any more sense?

-Dave
Top
Thu, 17 Mar 2016 - 5:55 PM
#6
Nixonfamilyusa
Offline
Last seen: 7 weeks 5 days ago
Title: ★
Joined: 17 Mar 2016
Posts: 6
perfect thank you
I followed you directions and it worked well. I can design a form and use a button to navigate to another form.

The next step is saving and closing a form with a button.
i tried: 


Sub CloseMacro()
	Dim myForm as object
	Set myForm = Application.AllForms("Appearance")
	Close Application.AllForms("myForm")
End Sub

and also tried:


Sub closemac
	DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close
End Sub

The second one works in Access but neither work in Base.
Any suggestions?

thanks
Shaun

Top
Thu, 17 Mar 2016 - 8:05 PM
#7
echo8hink
Offline
Last seen: 7 weeks 4 days ago
Title: ★★★
Joined: 10 Mar 2016
Posts: 21
closing form with button
Closing the form with a button is so easy in Access. I suppose it’s too easy to close the window… I know.

There are several ways. Using Access2Base I use “mClose” see code:


Sub FormClose
	DoCmd.mClose(acForm,"Foods") 'use your form's name in the quotes
End Sub

There is a save option parameter for mClose. I think it’s acSavePrompt as third (optional) parameter?? Sorry. I use it to close a menu form with no data…

-Dave
Top
Fri, 18 Mar 2016 - 6:55 AM
#8
JPL
Offline
Last seen: 10 weeks 3 days ago
Title: ★★★
Joined: 9 Feb 2013
Posts: 22
To close a form
An alternative way of closing a form is to use the Close method on the form object, like

Forms("myForm").mClose

The use of mClose instead of Close (like in MSAccess) seems strange, indeed. This is simply because Close is a reserved word in LO Basic. This forbids to write a Sub, a Function or a Method with Close as name.

JPL

Top
Fri, 18 Mar 2016 - 7:17 AM
#9
Nixonfamilyusa
Offline
Last seen: 7 weeks 5 days ago
Title: ★
Joined: 17 Mar 2016
Posts: 6
Thanks JPL
I appreciate your insight thanks. I never Knew Close was a reserved word. mClose is working well for me. It’s going to take some time but i am definitely going to learn Base from the ground up. I am starting on Mariano’s tutorial downloaded from the Forums today.

I took a look at the Close statement on the A2B extension website and it is making more sense to me. I also used an example provided by another poster as well to come up with:


Sub CloseMacro1
 	DoCmd.mClose(acForm,"Strength",acSavePrompt)
End Sub

There is a learning curve here but definitely easier than Uno or Star Office Basic.

thanks
Shaun

Top
Fri, 18 Mar 2016 - 7:22 AM
#10
Nixonfamilyusa
Offline
Last seen: 7 weeks 5 days ago
Title: ★
Joined: 17 Mar 2016
Posts: 6
Thanks Again
Dave, I appreciate all your help. I am working on a project for my 13 year old son and this has helped so much. I am helping him develop a worksheet for brainstorming character personality, traits etc if you want to write a novel. A way of easily storing: demographic info, personality, character traits, likes, dislikes, myers briggs, and a whole slew of other info to help develop more realistic characters for your novel. this way he will be able to quickly and easily navigate to a character and ensure that their traits are consistent throughout the book he wants to write. to make it easier i have included a lot of look up lists with combo boxes so he can simply click and select, giving him options.

It has been a fun project, maybe it will even help him.

I did add the save function to my macro, it now looks like this:


Sub CloseMacro1
 	DoCmd.mClose(acForm,"Strength",acSavePrompt)
End Sub

Top
Sat, 19 Mar 2016 - 10:31 AM
#11
Steve R.
Offline
Last seen: 1 week 2 days ago
Title: ★★★
Joined: 15 Dec 2011
Posts: 29
Great Discussion
Great discussion. I appreciate reading how self-identified problems are presented with a solution and seeing the working code. Very helpful for spreading knowledge so that the figurative “wheel” does not have to be “reinvented”. Thanks.

Top

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.