Changing multiple form aspects based on a button clicked

[Thomasso]

Hello,

I have an Orders Table. There can be a Supplier or a Customer Order. I have quite similar needs for tracking those so I would like to have one set of forms and set several properties (depending on which button is clicked - Supplier orders, Customer orders).

These are the things I need to change:
- Record source is the orders overview form
- Some text labels
- Button captions
- Default values in some forms
- Row source of some combo boxes

All of this would be done on click event (supplier order and customer order buttons) in VBA.

I tried to implement this and I have run into this problem:

RUNTIME 2465 (Application-defined or object-defined error. (it has to do something with changing the rowsource of a combobox.

Here is the code I have:





Unfortunately I am not able to upload my database to this forum, I tried it a dozen times with an error popping every time.

Am I approaching this correctly? What does the error mean?


Thanks everybody!
Tomas

[Bulzie]

Maybe do the rowsource as a query instead of the "Select..." to make sure it works. then just do the Rowsource = "qryxxx" Do both combo boxes use same number of fields?

[Micron]

If you want to upload, you should compact/repair your db but you pretty much MUST zip it. Whether a problem like that or one related to your actual db, more information on errors or failed trials helps us to help you by minimizing the guessing. The error usually means there is something wrong with how you're referencing an application level object or property, or an object - often due to spelling.

You are approaching this the right way IMO (using a form for multiple purposes). Don't know what drives the opening of your form, but you might want to consider using the OpenArgs property of DoCmd.OpenForm to pass the 'mode' to the form Open event as long as you're not trying to manipulate data in this event (for that, you'd use the Load event).

[Thomasso]

Could you give me an example how that might work? What king of query?

Yes, this combobox is a list of companies, and I either need to show all the suppliers or all the customers. There are 2 columns, CompanyName and CompanyType (which is the bound one and is Integer - 1 for supplier, 2 for customer)

Tomas

[CJ_London]

rather than providing an image of the code, copy and paste the code into the post and use the code tags (#). At the moment it is not possible to read your code unless you go to a separate page which means it is not visible when trying to respond.

To upload your db, remove all tables/forms etc not relevant to your question, then 1. compact the db. Then 2. zip it and 3. upload the zipped file by going to the advanced editor (click 'go advanced' button below)

with regards the error, it sounds like you are trying to reference an object that doesn't exist

[Thomasso]

If you want to upload, you should compact/repair your db but you pretty much MUST zip it.

I did zip it, still wouldn't upload. Probably a forum issue.

Don't know what drives the opening of your form, but you might want to consider using the OpenArgs property of DoCmd.OpenForm to pass the 'mode' to the form Open event as long as you're not trying to manipulate data in this event (for that, you'd use the Load event).

An user clicks a button in the main menu, that drives the opening. He can click "SUPPLIER ORDERS" or "CUSTOMER ORDERS". Multiple forms need to be edited (overview, data entry).

What is the difference between Open and Load event?

Never heard of OpenArgs, I am quite new to Access. Would it eliminate the error?

Tomas

[Thomasso]

To upload your db, remove all tables/forms etc not relevant to your question, then 1. compact the db. Then 2. zip it and 3. upload the zipped file by going to the advanced editor (click 'go advanced' button below) with regards the error, it sounds like you are trying to reference an object that doesn't exist

I did all that, still doesn't work. Must be a problem in this forum.

Here is the code:

Private Sub Příkaz6_Click()
    DoCmd.Close acForm, "frmHlavniMenu", acSavePrompt
    DoCmd.OpenForm "frmObjednavkyPrehled"
    
    Forms!frmObjednavkyPrehled.RecordSource = "qryObjednavkyZak"
    Forms!frmObjednavkyPrehled.txtNadpis.Caption = "PŘEHLED OBJEDNÁVEK"
    Forms!frmObjednavkyPrehled.cmdNew.Caption = "Nová objednávka"
    
    DoCmd.OpenForm "frmDEObjednavky"
    Forms!frmDEObjednavky.cboFirma.RowSource = "SELECT tblFirmy.NazevFirmy FROM tblFirmy WHERE tblFirmy.FKTypFirmyID = 2 ORDER BY tblFirmy.NazevFirmy;"
    DoCmd.Close acForm, "frmDEObjednavky", acSaveYes
End Sub

it sounds like you are trying to reference an object that doesn't exist

Very unlikely, I have triple checked the names.

Tomas

[CJ_London]

There are 2 columns, CompanyName and CompanyType

Assuming your table structure is based on what we went through a few weeks ago, this should be three columns. You need to include a companyID which your combo bound column will be set to. Also you shouldn't need to include the companytype column in your query return, you just need to use it for your criteria, everything else should be working off the companyID.

Micron's suggestion is also a good one

@Micron

You are approaching this the right way IMO (using a form for multiple purposes)

Glad you agree!

[Micron]

I did zip it, still wouldn't upload. Probably a forum issue.
2. What is the difference between Open and Load event?
3. Never heard of OpenArgs, I am quite new to Access. Would it eliminate the error?
Tomas

2 - Simply put, you can manipulate form properties on Open, but not data related to the source because Load comes after, which is when the records get loaded. Google ms access form events to get the whole picture as there are several, all occurring in a specific order from start to finish.
3 - No, it won't. It was intended to suggest how to tell the form "set up for customer order" versus "set up for vendor order". In the form Open event, one of the first things you'd do is determine what I call the form opening mode:

If Me.OpenArgs = "vendor" Then
  do vendor stuff...
Else
  do customer stuff
End If

[Thomasso]

Okay, I slept on it and guess what, guys... I forgot to rename the actual combo box to cboFirma. Oh my god, 3 days of rage and for this? Anyway, thanks for every advice you gave me.

Tomas

[Thomasso]

I have one more question. How do I change event action through VBA? Let me elaborate...

When someone clicks "CUSTOMER ORDERS", the form will open with an overview of all customer's orders (continuous form). There is a button next to each record to open order details. How do I set that button's click event when I click the "CUSTOMER ORDERS" button?

EDIT:
RowSource is acting weird, see code below:

    
DoCmd.OpenForm "frmDEObjednavky"


Forms!frmDEObjednavky.cboFirma.RowSource = "SELECT tblFirmy.NazevFirmy, tblFirmy.PKFirmaID FROM tblFirmy WHERE tblFirmy.FKTypFirmyID = 2 ORDER BY tblFirmy.NazevFirmy;"


MsgBox Forms!frmDEObjednavky.cboFirma.RowSource


DoCmd.Close acForm, "frmDEObjednavky", acSaveYes
DoCmd.OpenForm "frmDEObjednavky"


MsgBox Forms!frmDEObjednavky.cboFirma.RowSource


DoCmd.Close acForm, "frmDEObjednavky", acSaveYes

The first MsgBox shows correct RowSource (the SQL statement), the second MsgBox is empty (as well as the actual row source, why?

Thanks again,
Tomas

[CJ_London]

it's because you have closed the form


DoCmd.Close acForm, "frmDEObjednavky", acSaveYes
DoCmd.OpenForm "frmDEObjednavky"


MsgBox Forms!frmDEObjednavky.cboFirma.RowSource

[Thomasso]

Ah, is there any way that I can save this permanently?

That form is for entering data and is only opened when needed, not right away. I only opened it so I could set the parameters, then I immediately close it.

It works like this:
- after user click on button "CUSTOMER ORDERS", the orders overview form appears.
- there's a button "NEW ORDER", when that is pressed, I need it to have that rowsource

- when user clicks "SUPPLIER ORDERS" and then presses "NEW ORDER", I need the row source to be modified.

How do I approach this?

Tomas

[ssanfu]

Just wanted to point out a couple of things.....

DoCmd.Close acForm, "frmDEObjednavky", acSaveYes

The acSaveYes doesn't do what you think it does.
You change the combo box row source in code, but the change is not saved when you close the form.
The acSaveYes is for saving changes to the DESIGN of the form. If you use code to open the form in design mode, make changes to the form, THEN the acSaveYes WILL save the changes to the FORM.
If you changed the combo box row source (using code) WHILE IN DESIGN MODE and used acSaveYes, the change to the combo box would be saved.

There is a button next to each record to open order details.

You DO realize there is only 1 button, irregardless if there is one record or one hundred records?
Look at the form in design view. How many buttons are there? Only one.
So you cannot hard code the FK value in the WHERE clause (" WHERE tblFirmy.FKTypFirmyID = 2"), because the FK value will ALWAYS be 2 - for every Customer. (but maybe there is other code that is not shown to create the row source query).

[Micron]

If you look closely at my last sentence in post 3 you're going to hate yourself even more!