Convert Macro to VBA to open forms

[Udaman]

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!

 

[Udaman]

Unfortunately my lack of Access terminology is confusing the both of us, but at least I am learning some good terminology with this post, so thank you for that!

Here is the SQL of my Query. I fixed the expressions (the program automatically put the brackets in for me).

SELECT CylinderTests.AssetNo, CylinderTests.WONumber, CylinderTests.Misfire1Result, CylinderTests.Misfire1Note, CylinderTests.Misfire2Result, CylinderTests.Misfire2Note, CylinderTests.Misfire3Result, CylinderTests.Misfire3Note, CylinderTests.Misfire4Result, CylinderTests.Misfire4Note, CylinderTests.Misfire5Result, CylinderTests.Misfire5Note, CylinderTests.Misfire6Result, CylinderTests.Misfire6Note, CylinderTests.Misfire7Result, CylinderTests.Misfire7Note, CylinderTests.Misfire8Result, CylinderTests.Misfire8NoteFROM CylinderTests
WHERE (((CylinderTests.AssetNo)=[Forms]![frmWorkOrderSummary].[AssetNo]) AND ((CylinderTests.WONumber)=[Forms]![frmWorkOrderSummary].[WONumber]))
ORDER BY CylinderTests.AssetNo;

I am still a bit confused when you say the expression goes in the Query field criteria row. Did I not put it in the right place?

 

[Udaman]

Oops, duplicated post, didn't appear at first.

 

[Micron]

From what I see of the sql statement, it looks right. If you look at that query in design view, you should see the applicable Forms! expression in the criteria row for two fields. I see no way of including an image in my reply, otherwise I'd include one. If you still don't understand, look here at the example referencing the State_Choice control on Form1.

Dependent Combo Box in Access

 

[Udaman]

Well now when I pick the button on the main form to open the "CylinderTestsQueryForm" form, a dialog box comes up and asks me for the AssetNo and WONumber. If I type them in manually, or just press OK, the form opens but they are still blank. When I press Cancel and do a debug on it, the error is here (in bolt font):

Private Sub btn2500CylinderTests_Click()Me.Visible = False
[B]DoCmd.OpenForm "CylinderTestsQueryForm"[/B]
End Sub

Just to provide clarification (in case it helps), here are the items I am using for this information:

Tables:
CylinderTests
WorkOrderSummary

Queries:
CylinderTestsQuery
WorkOrderSummaryQuery

Forms:
CylinderTestsQueryForm
WorkOrderSummary

So now I am really stumped. Could the problem be somewhere on my WorkOrderSummary form where it's trying to pull the info from?

 

[Micron]

So you made this query the record source for the form, and you made the control source for the form Asset control to be the asset field from that query, and you made the control source for the work order control on the form to be the work order field from that query? It sound like no.

You cannot bold text wrapped in code tags here, so I can't tell which is the problem line. Note my signature tip #1 re error numbers/text. It may be nothing to worry about given that you're indicating it was generated upon cancelling (what I don't know exactly, but assume it is the form opening you cancelled). One thing you can also try is open the main form and enter those two values. Ensure those two values actually exist in your table(s) in a record so that the query would retrieve records based on them. THEN open the query. If you get a prompt, something is not right, like the name of the control or name of the form you've entered in the query criteria fields is not correct; i.e. not spelled correctly. IF you don't get prompted and you get no records and you know those values exist, then copy the query, open the copy and replace the Forms! part in the query criteria for those fields with those values then run the query. That is, using those values, put the work order number in the work order field and the asset number in that field. If you still get no records, there is something else wrong with the query; like maybe table joins.

If you don't have it solved after this, then either you need to put a db copy in a drop box somewhere, or I will need to create a very simple one that I can let you download. It's taking too long to solve what is not very complicated. Maybe I'm not communicating the requirements very well, or you are reading my answers too fast and not zeroing in on what you are supposed to be doing.

 

[leol2]

Hello.
This is a helpful article How to Convert Access Macro to VBA code

 

[Udaman]

I had to take a break from this issue for a few days to jump onto other more pressing issues, and I am glad because I was able to come back with a fresh perspective. The solution turned out to be way too simple.

On the main form, for the Click Event Procedure I used this code:

Private Sub btn2500CylinderTests_Click()'Transferring Field Values from FormA to FormB via the Click() Event of a Command Button
DoCmd.OpenForm "CylinderTestsQueryForm"
 
Forms!CylinderTestsQueryForm![AssetNo] = Me![AssetNo]
Forms!CylinderTestsQueryForm![WONumber] = Me![WONumber]
End Sub

It works perfectly.

I also was able to better understand the method you were trying to use Micron, but I suspect there was something wrong with how I originally created my forms or something. I actually used your method for another issue and it worked, so I can't say why it wasn't working for this scenario, but at least I found another method that works too.

Thanks for all the help and patience!

 

[Micron]

Glad you got it solved.