Trouble getting a where command to work

[evilcowstare via AccessMonster.com]

Hi, I am having some trouble using the DoCmd.OpenForm function.

Instead of using the code directly I have been using a Macro to open a form
and then in the where command trying to make the ID on one form go to the
other...

So whichever ID number is on one form, when I run the macro it should open up
another form and in the combo box show the same id number.

I just seem to be having trouble getting it to work, maybe if I say what im
trying to do....

I have a customer table which a form runs off, so you enter new customers and
when you save it it gets given an ID.

On another form I am trying to display the same customer information at the
top of the page, to do this what im planning on doing is having an invisible
combo box which will get and display the same ID from the customers form.
I am then using an AfterUpdate command of CustomerName = Me.CustomerIDCombo.
Column(2)
To then display the bits in seperate text boxes.
The problem I have is that I cant get the CustomerIDCombo on the second form
to pick up the ID no form the 1st Customer Form.

The ID box on the customer form is called "customerID"
The combo box on the next form is called "CustomerIDCombo"
I want "CustomerIDCombo" to gets its selection based on the number that is
shown in "CustomerID"
Im planning on using a button on the 1st customer form to when clicked runs
DoCmd.OpenForm Where etc etc either in code or macro.

Can anybody help me out?
I am new to this so please keep it straight forward if poss )

Thank You for all your help!!!!!!!!!!!!!!

 

[Joan Wild]

Hi, I am having some trouble using the DoCmd.OpenForm function.

Instead of using the code directly I have been using a Macro to open a
form
and then in the where command trying to make the ID on one form go to the
other...

I have a customer table which a form runs off, so you enter new customers
and
when you save it it gets given an ID.

On another form I am trying to display the same customer information at
the
top of the page, to do this what im planning on doing is having an
invisible
combo box which will get and display the same ID from the customers form.
I am then using an AfterUpdate command of CustomerName =
Me.CustomerIDCombo.
Column(2)
To then display the bits in seperate text boxes.
The problem I have is that I cant get the CustomerIDCombo on the second
form
to pick up the ID no form the 1st Customer Form.

Why do you need a combobox on the second form at all? Since you are opening
the form and showing just that one customer, the combo seems unneeded (just
use a textbox control). Can I suggest you just base the second form on the
customer table, and add controls bound to the CustomerID and CustomerName
fields. You don't need the 'CustomerID' control on the second form for this
to work.

Im planning on using a button on the 1st customer form to when clicked
runs
DoCmd.OpenForm Where etc etc either in code or macro.

DoCmd.OpenForm "NameofSecondForm", , , "CustomerID = " & Me.CustomerID

 

[evilcowstare via AccessMonster.com]

Hi Joan
The second Form is already based on another table, I am using tabs for
different information against each customer which is stored in a seperate
table.
How do I go about connecting/bounding the ID or Name on the second form to
the one on the First?

Thank You !

Hi, I am having some trouble using the DoCmd.OpenForm function.

[quoted text clipped - 19 lines]

form
to pick up the ID no form the 1st Customer Form.

Why do you need a combobox on the second form at all? Since you are opening
the form and showing just that one customer, the combo seems unneeded (just
use a textbox control). Can I suggest you just base the second form on the
customer table, and add controls bound to the CustomerID and CustomerName
fields. You don't need the 'CustomerID' control on the second form for this
to work.

Im planning on using a button on the 1st customer form to when clicked
runs
DoCmd.OpenForm Where etc etc either in code or macro.

DoCmd.OpenForm "NameofSecondForm", , , "CustomerID = " & Me.CustomerID

 

[Joan Wild]

So you are saying that the second form's recordsource does not contain the
ID or the Name? If so, it won't be bound to anything. So you can display
the value in a textbox but it won't be bound or tied to anything.

Set its control source to =Forms!NameOfFirstForm!CustomerIDCombo.Column(2)


--
Joan Wild
Microsoft Access MVP

Hi Joan
The second Form is already based on another table, I am using tabs for
different information against each customer which is stored in a seperate
table.
How do I go about connecting/bounding the ID or Name on the second form to
the one on the First?

Thank You !

Hi, I am having some trouble using the DoCmd.OpenForm function.

[quoted text clipped - 19 lines]

form
to pick up the ID no form the 1st Customer Form.

Why do you need a combobox on the second form at all? Since you are
opening
the form and showing just that one customer, the combo seems unneeded
(just
use a textbox control). Can I suggest you just base the second form on
the
customer table, and add controls bound to the CustomerID and CustomerName
fields. You don't need the 'CustomerID' control on the second form for
this
to work.

Im planning on using a button on the 1st customer form to when clicked
runs
DoCmd.OpenForm Where etc etc either in code or macro.

DoCmd.OpenForm "NameofSecondForm", , , "CustomerID = " & Me.CustomerID

 

[evilcowstare via AccessMonster.com]

Hi Joan
Sorry Ill try and explain it a bit better..

The two forms have 2 tables as...
CusomerForm = "customer" table
JobsForm = "jobs" table

Both the 1st and 2nd forms have thier own ID numbers.
The 1st is the ID of the customer and in the 2nd is the Job No

I need the JobsForm to display the current customers details off the
CustomerForm and show it in a new record, to which when it is saved will
store all the other details plus the customers details in the table "jobs"
So effectively the cusomters details are stored twice, once in the
CustomerForm when they are first created and again in the JobsForm once that
is saved.

Maybe this will help???

Sorry Im still learning but getting there! )

Jay

So you are saying that the second form's recordsource does not contain the
ID or the Name? If so, it won't be bound to anything. So you can display
the value in a textbox but it won't be bound or tied to anything.

Set its control source to =Forms!NameOfFirstForm!CustomerIDCombo.Column(2)

Hi Joan
The second Form is already based on another table, I am using tabs for

[quoted text clipped - 27 lines]

 

[evilcowstare via AccessMonster.com]

In case I missed something the second table "jobs" also includes ALL the
fields off the "customers" one inc Name and customer ID.

Thanks

Hi Joan
Sorry Ill try and explain it a bit better..

The two forms have 2 tables as...
CusomerForm = "customer" table
JobsForm = "jobs" table

Both the 1st and 2nd forms have thier own ID numbers.
The 1st is the ID of the customer and in the 2nd is the Job No

I need the JobsForm to display the current customers details off the
CustomerForm and show it in a new record, to which when it is saved will
store all the other details plus the customers details in the table "jobs"
So effectively the cusomters details are stored twice, once in the
CustomerForm when they are first created and again in the JobsForm once that
is saved.

Maybe this will help???

Sorry Im still learning but getting there! )

Jay

So you are saying that the second form's recordsource does not contain the
ID or the Name? If so, it won't be bound to anything. So you can display

[quoted text clipped - 7 lines]

 

[Joan Wild]

Well that is your problem; the table design. There is no need to store all
the customer information in the Jobs table. You only need the CustomerID in
the Jobs table. Store information in only one place. In the relationship
window, you would create a relationship between the Customers Table and the
Jobs table - linked on CustomerID.

If you use the Forms Wizard, you can create a main form (based on the
Customer table) with a subform (based on the Jobs table). The wizard also
gives you the option of making the Jobs form a separate form rather than a
subform.

Detailed steps:
Create new Form using Form Wizard
Choose the Customer table; add the fields you want
Choose the Jobs table; add the fields you want
Click Next, and because you've defined a relationship between the two
tables, Access will give you the option of 'subform' or 'linked form'


--
Joan Wild
Microsoft Access MVP

In case I missed something the second table "jobs" also includes ALL the
fields off the "customers" one inc Name and customer ID.

Thanks

Hi Joan
Sorry Ill try and explain it a bit better..

The two forms have 2 tables as...
CusomerForm = "customer" table
JobsForm = "jobs" table

Both the 1st and 2nd forms have thier own ID numbers.
The 1st is the ID of the customer and in the 2nd is the Job No

I need the JobsForm to display the current customers details off the
CustomerForm and show it in a new record, to which when it is saved will
store all the other details plus the customers details in the table "jobs"
So effectively the cusomters details are stored twice, once in the
CustomerForm when they are first created and again in the JobsForm once
that
is saved.

Maybe this will help???

Sorry Im still learning but getting there! )

Jay

So you are saying that the second form's recordsource does not contain
the
ID or the Name? If so, it won't be bound to anything. So you can
display

[quoted text clipped - 7 lines]

DoCmd.OpenForm "NameofSecondForm", , , "CustomerID = " & Me.CustomerID

 

[evilcowstare via AccessMonster.com]

Ok Ill give it a go, thanks very much for your help Joan!!!

Well that is your problem; the table design. There is no need to store all
the customer information in the Jobs table. You only need the CustomerID in
the Jobs table. Store information in only one place. In the relationship
window, you would create a relationship between the Customers Table and the
Jobs table - linked on CustomerID.

If you use the Forms Wizard, you can create a main form (based on the
Customer table) with a subform (based on the Jobs table). The wizard also
gives you the option of making the Jobs form a separate form rather than a
subform.

Detailed steps:
Create new Form using Form Wizard
Choose the Customer table; add the fields you want
Choose the Jobs table; add the fields you want
Click Next, and because you've defined a relationship between the two
tables, Access will give you the option of 'subform' or 'linked form'

In case I missed something the second table "jobs" also includes ALL the
fields off the "customers" one inc Name and customer ID.

[quoted text clipped - 32 lines]