OpenForm?

[franklucas]

Hi all,I have 2 Forms, Form1 (with Devices) and Form2 (with Components).In the Form1 I have got list of Devices (with ID_Device, the field name txtDevice). I have got list with 70 devices, when I click on the ID_Device (1,2,3,etc) i I'd like to open Form2 related on the ID_Device from Form1. I'd like to see only this Compoment wchich i choose in the Form1.I am using Access 2007 and i try write OpenForm in VBA and i always fail.Please help me.

[CreganTur]

Welcome to the forum! Always good to see new members.

You can use 'DoCmd.OpenForm' to accomplish this- you just need to use the WHERE parameter. It's an optional parameter of the OpenForm method.

For instance:
DoCmd.OpenForm "Employees", , ,"LastName = 'King'"

The above code will open the Form named 'Employees' where the LastName field has a value of King. Notice that King is wrapped in single-quotes. This is very important to know, because leaving these out will cause an error.

Since you want to open your Form based on the value of a control on your Form1, then you'll be referencing the value of the object. So your code would look something like (this is untested and just an educated guess for example purposes):
DoCmd.OpenForm "Form2",,,"ID_Device = '" & Me.ID_Device & "'"

HTH:thumb

[franklucas]

Hi,
when I use:
DoCmd.OpenForm "Form2", , , "ID_Device = '" & Me.ID_Device & "'"
then
Form2 will open but with all records of Components, not reletated with ID_Device from Form1.

Any idea?

[CreganTur]

What kind of control do you have on Form1 where the User can choose which components they want to see? (the ID_Device)

Is the ID_Device field shared between Form1 and Form2?

[franklucas]

Yes,
ID_Device is in Form1 and Form2.

[CreganTur]

Yes,
ID_Device is in Form1 and Form2.

Can you upload a test version of your database that includes the 2 forms and some extraneous data for testing? It would make it easier to see what's going on.

[franklucas]

It will be dificult because my database is in Polish language.

[CreganTur]

I've attached a sample database that demonstrates how to use the OpenForm method with a WHERE parameter.

Open frmChooseOrder, make a choice from the combobox, and press the command button. It will open another form to the record that matches the chosen order.

When you look at the VBA you will notice tha the single quotes are not wrapped around the combobox reference- this is because I'm working with a number data type. You only have to use the single quotes when you are working with a string. If you end up working with a date, then you would use pound (#) signs instead of single quotes.

HTH:thumb

[franklucas]

Thank you for helping me.
I do everything like in your project but when I click on ID_Device in form1 the second form2 was open all component without relation.

I think the problem is that I have list of Device (not combo and button) and when we click on ID_Device then should run a macro.

I said that I have my project in Polish language, and I also using connection to SQL Server database that is problem with sending you my project.

I prepare screen shoots of my project and maybe can you help me.

[franklucas]

Hi again,
I rebuild your project and add two new forms Orders and Details. This is similar like in my project and there is the same problem, when we click on OrderID then is open another forms but with every record not related with OrderID.

[CreganTur]

I've been looking into this and I think the problem is due to your use of datasheet view. Datasheet view doesn't support the OpenForm method. If you look at my example, it uses Form View and a button, as you noticed.

If you cannot use Form view, then look into using SubDatasheets- it won't open a new window to show the details, but it will show all of the orders and order details in a hierarchical tree in datasheet view.

[nepotist]

That was some help to me too thank you