Values don't get passed through Macros

[ailyn]

All my forms work with buttons that are controled by Macro's with the OpenForm option.
But when I click the form that opens doesn't pass the value it should. Like from sellers to orders, it doesn't show the order of that seller. From products to orders it doesn't show that product and so on. I thought that was automatical. What should I do? The tables are related by Ids as they should.http://vbaexpress.com/forum/images/smilies/banghead.gif

[Norie]

Are you using the Where Condition argument in your macros?

[ailyn]

yes, I do. both forms have in common 'SellerId' so I put that just like '[SellerId]' in the Where value from the macro. I also tried using the builder (...) and got the values on each form joined by 'Like' and still it didn't work. How should I filled them in the Where to make it work?

[chocobochick]

Using just "[SellerId]" looks more like a filter to me. If you're using the Where Condition box to select your data, you need to relate your critera to a specific field with an operator. In this case, you'd probably use a line like this:

[SellerId] = Forms![NameOfFormWithButton].[NameOfControlWithSellerId]

Hope that helps!

[ailyn]

Thanks but that option doesn't work & I'm sure I spelled it correctly because I used the (...) build option too:
[SellerId]=Forms![Sellers].[SellerIdctrl]

When I save it it changes it automatically to:
[SellerId]=[Forms]![Sellers].[SellerIdctrl]

& when I try the button to see if it works it shows a popup window that asks for Forms![Sellers].[SellerIdctrl]

I think that it's just access that hates me T.T

[chocobochick]

Oops. Try [SellerId]=[Forms]![Sellers]![SellerIdctrl] instead.

If that doesn't work, doublecheck the form and control names one last time.

[ailyn]

Thanks but I tried that too. When I click the button it tells me to run the macro step by step. I click on step and it asks for the id. The same goes for the other forms. All fields and tables are properly related so I don't understand why it always gives me the same errors. Have I turned some option of access off without realising or something?

[geekgirlau]

Might be a stupid question, but is the form Sellers still open at this point?

[chocobochick]

If it's giving you a popup asking for a value, it means Access can't find the field or control you're referencing. This is usually just a small but simple oversight that needs to be corrected. So in breaking down the Where Condition expression:

[SellerId]=[Forms]![Sellers]![SellerIdctrl]

SellerId should be the name of the field in the table used by the form you're attempting to open. Make sure the form references the correct table as its Record Source, and make sure SellerId is the name of the table field, not the form's control.
Sellers is the name of the form holding the SellerIdctrl control carrying the information you wish to select in the newly opened form. As geekgirlau stated, this form needs to be opened for Access to find it, but I've been assuming this form also carries the form-launching button you're attempting to program.
SellerIdctrl should be the name of the control in the open form holding the criteria for your selection. Make sure you're referencing the control's name and not the field for the table which it is based upon.
Also, make sure SellerIdctrl is returning the value you're looking for, especially if you're using a Combo Box or List Box with multiple columns for this. You can easily test the value it sends by putting a new textbox control on the same form and typing "=[SellerIdctrl]" inside it. When you open the form to a record, this textbox will show you what SellerIdctrl is returning, whether or not it's the value you wanted.

If none of this helps, try providing us with a more detailed, specific, step by step account of the error messages that occur when you attempt to use this button. I don't think I've ever been asked by Access to run the macro step by step before.

[GaryB]

Hi everyone,

I'm a little confused by this? If this is a button based on a macro and the openforms command is being used, then wouldn't clicking on the arrow for form name and selecting the form you want opened almost be automatic or I am totally missing the point, which by the way happens often

Gary

[chocobochick]

If all you wanted to do was open the form with default settings, then yes, you get some help in the form of combo boxes listing available form names.

But no such help appears when filling in the Where Condition textbox for that macro command. If you're trying to filter the new form's recordset using an SQL clause constructed from the data a user has entered in another form's control, you're kind of on your own.

[ailyn]

Well, the truth is that I gave up on that one and included the form I wanted to open as a subform. That always works.

But the problem of the id's and the macro is the same with all my forms. Here you have some more details on one of them:
tbl orders:
orderId
orderdate
sellerId (=tblsellers)
transport
expecteddate
freightcharges
dicount %
VAT %
CustomerId (=tblcustomers)
Paid (yes/no)
Gone (yes/no)

tblorderdetails:
orderdetailId
orderId (=tblorders)
productId(=tblproducts)
blowerId (=tblblowers)
price ?
extras ?
extrasdescription

tblproformas:
proformaId
ProformaNr
ProformaDate
OrderId(=tblorders)

Frmorders has: tbl orders as main form and tbl orderdetails as subform
Frmproformas has: tbl proformas ans tblorders as main form and tblorderdetails as subform.
All forms acquired their fields with the wizard that names them after their correspondant field in the table. so in the form orders ctrl 'orderId' = tbl fld 'orderId'.

In frmorders you click a button that opens the macro 'proforma : on click' = oprenform "proformas" Where '[OrderId] = [Forms]![orders]![orderId]'

Now for all what you explained me (thanks a lot by the way). This should work, but it doesn't it activates the pop-up that asks for orderId ???

[geekgirlau]

Are you able to attach a zipped copy of the database? If possible, strip all confidential or proprietory information out first.

[chocobochick]

Frmorders has: tbl orders as main form and tbl orderdetails as subform.
Frmproformas has: tbl proformas ans tblorders as main form and tblorderdetails as subform.
. . .
In frmorders you click a button that opens the macro 'proforma : on click' = oprenform "proformas" Where '[OrderId] = [Forms]![orders]![orderId]'

You didn't mention any form just named "orders." Did you mean this?

[OrderId] = [Forms]![frmorders]![orderId]

[ailyn]

Sorry for the huge delay! 'been extremely busy with an expo at work.

Well, chockobochick, thanks but it still doesn't work. Logically it should but it doesn't. Maybe another option in my form or db?

I have got ready the zipped version of my db but it is a bit too big 1.62Mbs. I could attach it here but i dunno if its allowed or even how to do it.

[xCav8r]

ailyn,

You still need to remove more stuff from your database to get the attachment size down to one acceptable by the forum. (Think that's around 250k.) Strip out everything that's not related to this problem. If the zipped size is still too large, trim down the rows in the tables until it zips to the appropriate size.

Attachments are not only allowed, but they're encouraged. They're the quickest means of getting a timely and focused reply to your problems. :)

To upload an attachment, look for something called "manage attachments" when you're posting in advanced mode.

[ailyn]

i don't know why but I've stripped the db of more than half its content and it's still the same size. How can that be?

[geekgirlau]

Did you compact it?

[chocobochick]

Tools / Database Utilities / Compact and Repair Database from the menu. :thumb

[xCav8r]

ailyn, if you don't compact it, it won't free up the space for the things you've removed. An alternative is to ZIP it instead. That significantly reduces the overall size, and it's the size of the ZIP--not the MDB--that matters for the forum. :)

[ailyn]

T.T I've tried everything already. The thing only goes down to 288Kbs qipped and all and that is too much to upload here! If I take anything else out it won't work for what I need to show.

[geekgirlau]

Hi Ailyn,

Okay, I think I've figured out the immediate problem.

If you have a look at your macro "Proforma : On Click" the "Where" condition on opening the form "Proformas" is as follows:

[OrderId]=[Forms]![Orders].[OrderId]

However if you look at the form, in the data source the "OrderID" field has been renamed as "Orders_OrderId". Therefore your "Where" criteria should be:

[Orders_OrderId]=" & [Forms]![Orders].[OrderId]

Notice also the "&". In simple terms, this is because we are inserting a value that comes from another source rather than directly from the form we are opening, or a static value such as "7".

I'm not a big fan of using macros for this kind of thing, although it is greatly a matter of personal preference. However if you create a button from the toolbar with the wizards turned on (the button that looks like a magic wand), it does most of the work for you. Try testing this out for your "Proforma" button - after you select "Open Form" as the action, and "Proforma" as the form you want to open, you can select "Open the form and find specific data to display" and this will step you through the process. It's a great way to get used to the language, especially when you're starting out.

Also have a look at the "Format_Current" code in the "Proformas" form. It's not quite right as I don't know where the Customer ID is supposed to come from, but generally speaking you only need to use a recordset if the value is not directly on the form. If you want to capture a value that is displayed on the form, you can use "Me.ControlName".

[ailyn]

Thaaanks!!

Well the code option fro the macro didn't work. It said that there was an error with the & or something so I tried the second option and it stopped asking for the [OrderId].
Now of course I've got another problem with the Proforma form because it doesn't open the record I wish unless I select it from a list and then when I try to close frmProformas it gives me a Jetengine error on [CustomerId] that it's supposed to be on tblOrders where it feeds. the thing is that it doesn't always have to be filled.

that's the reason why I added this code, so that when it doesn't exist I just don't show it. You know how to avoid the Jet error?

Private Sub Form_Open(Cancel As Integer)

If IsNull(CustomerIdctrl) Then
CustomerIdctrl.Visible = False
SellerIdctrl.Visible = True ' No customer record
Else
CustomerIdctrl.Visible = True
EditCustomers.Visible = False ' Customer exists
End If
End Sub