Convert Macro to VBA to open forms

Udaman

New Member
Joined
Feb 17, 2016
Messages
43
I have a main form called WorkOrderSummary where it is part information, and part navigation. In the Detail body I have several fields of information based on queries and tables. In the footer of the form I have numerous navigation buttons to forms that have in common the fields [AssetNo] and [WONumber] with the main form in the detail body.

What is currently working correctly is a form will open blank, and once I fill in the two fields, I can close it and the button will open it back up with those values in tact.

What I am trying to do is when I pick on one of these buttons, it will first look to see if a record exists with the two above fields. If so then it simply opens the form, but if not then I need it to automatically fill in those fields and create a new record.

I have found several posts and websites that touch upon this subject, but getting it to work with my forms has been met with failure so far. The option that seems to be the best is to use the OpenForm Method, utilizing OpenArgs in an expression. I am able to open a form properly when a record exists, and I can open a blank form when they don't exist, but I am stuck at automatically filling in the fields when no records exist. I am using a Macro to do this, and I am thinking I need to convert this to VBA code in order to provide multiple arguments.

Can I get some help with the VBA to accomplish this? One of the forms I am trying to open is called “CylinderTests”.

My macro looks like this:

OpenForm
Form Name: CylinderTests
View: Form
Filter Name: Blank
Where Condition: ="[WONumber]=" &[WONumber]
Data Mode: Blank
Window Mode: Dialog

[WONumber] is the main link between all of my forms. It's a field in the WorkOrderSummary table that is an auto-number. I will have several forms that use the same [AssetNO], but the [WONumber] is truly unique. It's combining of the two where I am hitting my wall.

Thanks in advance!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
What I am trying to do is when I pick on one of these buttons, it will first look to see if a record exists with the two above fields. If so then it simply opens the form, but if not then I need it to automatically fill in those fields and create a new record.
I guess there is at least a partial record, otherwise the values should not remain. It's not real clear as to what you want to do because when you refer to buttons and forms etc. it's not clear to which of them you're referring to.

What I think you're saying is that if there is a related record somewhere (not the one you created by inputting values then closing the first form) you want another form to open with the related record filled in. You seem to be saying you have that figured out. What's confusing is how you expect to automatically fill in fields on that form if there is no record??
I am stuck at automatically filling in the fields when no records exist
 
Upvote 0
I apologize for any confusion. I will try and summarize. From the form "WorkOrderSummary" there are two fields [AssetNo] and [WONumber] that come from tables Assets and WorkOrderSummary respectfully. These two pieces of information is what ties all of my other forms and tables together. Each of these other forms require these two pieces of information as part of a new record. So I created a button on the WorkOrderSummary form and it is a macro that opens up another form to a specific data, which is the WONumber. However, when I open the form for the first time, there is no data at all, I have to type in the [AssetNo] and [WONumber] manually. This creates a new record on that form. What I am trying to do is instead of typing in those fields manually, I want them to be filled out automatically based on the values on the WorkOrderSummary form where the buttons are located. Once those values are there, when I press the button to open that form again, they remain.

So much for being brief...

To perhaps help better understand what I am trying to accomplish, please allow me to further summarize. I have a table called Assets. I have about 600 assets at the moment. Each Asset will at some time need service. This is where I created a table called WorkOrderSummary, which is a list (autonumber) of work order numbers assigned to an Asset. One Asset can have multiple [WONumbers]. So when I create a new Work Order, I have to start with the [AssetNo] first, then it assigns a [WONumber] automatically. This all happens on the form WorkOrderSummary (Ya I know, my table and form are called the same thing, I realized this way too far in to change it). From this form WorkOrderSummary, I wish to open other forms, one in particular is called CylinderTests. When this form opens for the first time, it is a blank record. I then have to give it an [AssetNo] and [WONumber] manually by typing them in. Instead I want that form to already know what the [AssetNo] and [WONumber] is based on the WorkOrderSummary form. I assume that I do that with the button that opens it. If a record already exists, then the whole thing works great as it is. The reason this is important to get working, is because there are 15 possible forms that have to be opened and a record created in order to tie all of them together. So each time there is a new service, I have to open and type in the [AssetNo] and [WONumber] manually for each form. I don't want to do that anymore, LOL.

I hope this helps clarify things.
 
Upvote 0
Think I got it. You want to pass two values from one form to another. Solution depends on whether or not the second form is bound to a query or table, but moreso if those two controls are bound to anything. If they are not bound, as long as the first form remains open, you can simply enter an expression that points back to the first form.
=Forms!frmYourMainFormName.TheControlName
If you want, you can hide the first form on button click, then make it visible again on closing of the second form.

If the second form or those two controls are bound, let me know how and to what.
 
Upvote 0
You are correct, I'm just trying to pass those two values to another form.

Just about all controls are bound on every form due to the amount of tracking and reporting I need to do.

[AssetNo] is bound to table Assets
[WONumber] is bound to table WorkOrderSummary

When I create a new table, they all have included [AssetNo] and [WONumber] and are referenced back to their respective tables.

When I create a form I always make a Query first, then create the form based on the Query rather than directly from a table. That is the case here also. The form CylinderTests was created from query CylinderTestsQuery and [AssetNo] and [WONumber] are included in the Query.

Let me know if you need any more information.
 
Upvote 0
Then it ought to be a simple matter of referencing the main form controls as previously mentioned, but doing so in the query that the second form is based on rather than the controls on the second form. So the query fields would have Forms!frmYourMainFormName.TheControlName as criteria for each of the two fields coming from the first form. Your button simply opens the form - no arguments should be required unless you need them for something else such as locking/disabling any controls or whatever. If there is a record where BOTH of the two supplied fields have the values entered in the first form, it will open to that record, otherwise, it will open to (NEW) and you can start a new record. This assumes that the second form data properties are Data Entry: No; Allow Additions/Deletions/Edits are set to Yes. I'm also assuming you're not closing the first form when the second one opens. This makes it easier to repeat the process without having to start from scratch.

However, you need to handle the possibility that both forms are already open. Button click code to open the second form will simply activate it, showing whatever is already loaded in it. This could be confusing. Simply requerying this form during its open event would activate it correctly. This could be dangerous. You might lose any edits in progress on a record - especially if you've turned warnings off. Not knowing all the parameters, I'm going to suggest code that hides the first form and the only way to get it back is to close the second one, thus no worries and no prompting to close the second one.
In the button click event put
Code:
Me.Visible = False
DoCmd.OpenForm "CylinderTest"
Form CylinderTest needs a close event.
In the close event put Forms!WorkOrderSummary.Visible = True

All assumes I have your form names correct, otherwise, change to suit. If I knew more about the design, I might suggest passing to the procedure the name of the form to be opened instead of hard coding it. You'd pass the name of the form to the message as well.

Lastly, your comment about needing 15 forms suggests you might have a less than ideal design approach. I'm assuming you're creating a bunch of forms that will look almost exactly the same; one for each asset. OK if the forms are entirely different, but if not, you should try to minimize the object overhead. One way to do this is to reuse the same form for each asset, setting the record source according to the asset and disabling/hiding controls that don't apply. If the details are really that much different, a main form with a sub form control that holds the asset form is another approach. At least you wouldn't repeat all the main stuff that applies to each situation. Multiple forms is an easier to build approach for the beginner, though it can cause headaches later. Suppose you need to add one field to the main details. Now you have to redesign 15 forms instead of one. If that's not what you meant, sorry, and ignore the ramble.
 
Last edited:
Upvote 0
Thank you for the response. However I'm just a little confused on a couple things.

First, I assume you are saying to open up my query for CylinderTests, and remove [AssetNo] and [WONumber], and instead add the following expressions:

Code:
Expr1: [Forms]![frmWorkOrderSummary].[AssetNo]

Code:
Expr2: [Forms]![frmWorkOrderSummary].[WONumber]

Is this correct?

Next on the button to open the CylinderTests do I remove the macro I had completely and only use the VBA code you recommended? I ask because I tried it as noted and I am getting a dialog box asking me for "Forms!frmWorkOrderSummary.AssetNo", and the same for WONumber. Even if I provide those numbers, the form still opens up without those values shown. So I must be doing something wrong still.

I should mention that I opened up the CylinderTests form and deleted the AssetNo and WONumber controls, and inserted the expressions instead.

To answer your question about the multiple forms, each form is unique. The layout is similar for consistency, but all of the information is unique to each form.
 
Last edited:
Upvote 0
I assume you are saying to open up my query for CylinderTests, and remove [AssetNo] and [WONumber]
No, I'm saying in the criteria row for those two fields, reference the controls on the open form where these values have been entered by the user.
I opened up the CylinderTests form and deleted the AssetNo and WONumber controls, and inserted the expressions instead.
Wrong. I can't imagine where you'd put the expressions if you removed the controls. Without them, there's no way to get the records you want so no wonder you're getting prompted.

You do know how to create a query that has one or more criteria, yes? All you are doing is telling the second query to get the values it needs for its criteria (Work order number, asset number, whatever) from the controls on the first form that contain those values, by using the expressions. If you remove the controls from the form, how do you expect the query to find the values in them?

As for the macro, I don't use them so my suggestions are based on creating events and coding in the vb editor. If you know how to hide a form then unhide it when a different one opens by using macros, then go for it.
 
Last edited:
Upvote 0
My apologies for misunderstanding your instructions, I opened up Access for the first time 2 months ago, so I am learning by trial and error mostly.

I went ahead and replaced the controls [AssetNo] and [WONumber] back on my CylinderTests Query. Under each control's criteria I added the expressions as noted in my previous post, example:

Field: AssetNo
Table: CylinderTests
Sort: Ascending
Show:
Criteria: "Expr1: [Forms]![WorkOrderSummary].[AssetNo]"

Next I went back into my CylinderTests form and reinserted the original controls.

On my WorkOrderSummary form I modified the buttons code:

Code:
Private Sub btn2500CylinderTests_Click()Me.Visible = False
DoCmd.OpenForm "CylinderTests"
End Sub

When I press the button the CylinderTests form opens up and the WorkOrderSummary form is hidden, however the AssetNo and WONumber are still blank. I checked the Data Entry (no) and allow additions and edits are set to Yes on my CylinderTests form. I bet I am missing something simple here. I greatly appreciate your patience and help so far, I beg your favor to help me figure this out. Any suggestions?
 
Upvote 0
Under each control's criteria I added the expressions as noted in my previous post, example:
No again. Post #6 says the expression goes in the QUERY field criteria row. So does post #8 It also looks like you have messed up the expression since your post includes Expr1: as an addition to my original expression.
Your terminology is confusing me.
I went ahead and replaced the controls [AssetNo] and [WONumber] back on my CylinderTests Query.
Queries don't have controls, they have fields. Forms have controls (and it would be best if people didn't refer to them as fields, especially if they are not bound).
Post the sql of your query and I'll see if I can edit it as required.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top