New form with categories...

[stephendeloach via AccessMonster.com]

I have started a new db that needs to have categories in it. I have the Main
table with Operator, LeaseName, Vendor, Invoice, etc.. and the Category table
with all of my categories. What I am wanting to make is a form that has the
Main table as the main form and the Category table as the subform. In the
Main table I want to be able to enter my info and when I get to the Category
field be able to enter "3" for example and the subform automatically know to
put it in the "3" field in the table... Make sense? Thanks

Stephen

 

[Carl Rapson]

I have started a new db that needs to have categories in it. I have the
Main
table with Operator, LeaseName, Vendor, Invoice, etc.. and the Category
table
with all of my categories. What I am wanting to make is a form that has
the
Main table as the main form and the Category table as the subform. In the
Main table I want to be able to enter my info and when I get to the
Category
field be able to enter "3" for example and the subform automatically know
to
put it in the "3" field in the table... Make sense? Thanks

Stephen

If I understand what you're asking, it sounds like a combo box would do the
trick for you - no subform needed. Set the Row Source of the combo box to be
your Categories table, and bind the combo box (the Control Source property)
to the Category field in your Main table. When a category is selected in the
combo box, it will automatically be set in the Main table record.

If this isn't what you're talking about, you'll need to give more
information.

Carl Rapson

 

[stephendeloach via AccessMonster.com]

Is there a certain way I would need to link the tables?

I have started a new db that needs to have categories in it. I have the
Main

[quoted text clipped - 10 lines]

Stephen

If I understand what you're asking, it sounds like a combo box would do the
trick for you - no subform needed. Set the Row Source of the combo box to be
your Categories table, and bind the combo box (the Control Source property)
to the Category field in your Main table. When a category is selected in the
combo box, it will automatically be set in the Main table record.

If this isn't what you're talking about, you'll need to give more
information.

Carl Rapson

 

[stephendeloach via AccessMonster.com]

Yes there actually is more to it. (sorry its monday!) I think what I am going
to need is the subform to be a form with Quantity, UnitPrice, Description,
Total, and a check box to check if the total is Taxable. So this is where the
categories will come in. I need to be able to enter Category - "3" and the
subform know it is "Tools & Accessories" in the subform, for my Quaitity
UnitPrice etc... am I making more sense?? Thanks

Stephen

Is there a certain way I would need to link the tables?
[quoted text clipped - 12 lines]

Carl Rapson

 

[Carl Rapson]

Yes there actually is more to it. (sorry its monday!) I think what I am
going
to need is the subform to be a form with Quantity, UnitPrice, Description,
Total, and a check box to check if the total is Taxable. So this is where
the
categories will come in. I need to be able to enter Category - "3" and the
subform know it is "Tools & Accessories" in the subform, for my Quaitity
UnitPrice etc... am I making more sense?? Thanks

Stephen

Is there a certain way I would need to link the tables?

I have started a new db that needs to have categories in it. I have the
Main

[quoted text clipped - 12 lines]

Carl Rapson

So your subform is a list of items, like for an invoice or sales ticket? You
can still use a combo box bound to the category field, assuming you have a
category field in your items table. Set it up as I described, and in the
AfterUpdate event of the combo box you can populate a recordset from the
Categories table and assign values to the other fields in the record:

Dim rs as DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Categories WHERE Category="
& Me.cboCatList)
If Not rs.EOF Then
Me.txtDescription = rs.Fields![Description]
Me.txtUnitPrice = rs.Fields![UnitPrice]
' ... and so on
End If

Be sure to use your own table, field and control names in place of the
examples I've given.You can do something similar using ADO instead of DAO,
if you wish.

If this isn't what you mean, you'll need to give more details about your
form design and what you're trying to do.

Carl Rapson

 

[stephendeloach via AccessMonster.com]

Ok this is what I get. Compile Error: Method or data member not found. and
this is what i put in the AfterUpdate box of the Category combobox.

Private Sub Combo20_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Categories WHERE
Category=" & Me.Category)
If Not rs.EOF Then
Me.txtDescription = rs.Fields![Description]
Me.txtUnitPrice = rs.Fields![UnitPrice]
Me.TxtTotal = rs.Fields![Total]
Me.txtTaxable = rs.Fields![Taxable]
Me.txtQuantity = rs.Fields![Quantity]
End If

It highlights .txtDescription in blue
and Private Sub Combo20_AfterUpdate() in yellow....

My combo box is bound to the Categories table. My subforms record source is
SELECT Description.Quantity, Description.UnitPrice, Description.Description,
Description.Taxable, Description.Total, Description.Invoice FROM Description;
from description table and its name is description subform... if that helps
any. Thanks

Yes there actually is more to it. (sorry its monday!) I think what I am
going

[quoted text clipped - 14 lines]
So your subform is a list of items, like for an invoice or sales ticket? You
can still use a combo box bound to the category field, assuming you have a
category field in your items table. Set it up as I described, and in the
AfterUpdate event of the combo box you can populate a recordset from the
Categories table and assign values to the other fields in the record:

Dim rs as DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Categories WHERE Category="
& Me.cboCatList)
If Not rs.EOF Then
Me.txtDescription = rs.Fields![Description]
Me.txtUnitPrice = rs.Fields![UnitPrice]
' ... and so on
End If

Be sure to use your own table, field and control names in place of the
examples I've given.You can do something similar using ADO instead of DAO,
if you wish.

If this isn't what you mean, you'll need to give more details about your
form design and what you're trying to do.

Carl Rapson

 

[Carl Rapson]

What is the name of the control on your form that is bound to the
Description field? That's the control name you'll need to use in this
statement:

Me.txtDescription = rs.Fields![Description]

I used 'txtDescription' because I don't know the names of your controls. The
same applies to the other fields.

Another possibility: You have a field named Description in a table named
Description, and that could be a problem. Try changing the name of the table
or field and see if the problem persists.

Carl Rapson

Ok this is what I get. Compile Error: Method or data member not found. and
this is what i put in the AfterUpdate box of the Category combobox.

Private Sub Combo20_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Categories WHERE
Category=" & Me.Category)
If Not rs.EOF Then
Me.txtDescription = rs.Fields![Description]
Me.txtUnitPrice = rs.Fields![UnitPrice]
Me.TxtTotal = rs.Fields![Total]
Me.txtTaxable = rs.Fields![Taxable]
Me.txtQuantity = rs.Fields![Quantity]
End If

It highlights .txtDescription in blue
and Private Sub Combo20_AfterUpdate() in yellow....

My combo box is bound to the Categories table. My subforms record source
is
SELECT Description.Quantity, Description.UnitPrice,
Description.Description,
Description.Taxable, Description.Total, Description.Invoice FROM
Description;
from description table and its name is description subform... if that
helps
any. Thanks

<snip>

 

[stephendeloach via AccessMonster.com]

My record source for the Descriptions subform is SELECT [Descriptions].
Quantity, [Descriptions].UnitPrice, [Descriptions].Description, [Descriptions]
..Taxable, [Descriptions].Total, [Descriptions].Invoice FROM Descriptions;
does this help any? Thanks

What is the name of the control on your form that is bound to the
Description field? That's the control name you'll need to use in this
statement:

Me.txtDescription = rs.Fields![Description]

I used 'txtDescription' because I don't know the names of your controls. The
same applies to the other fields.

Another possibility: You have a field named Description in a table named
Description, and that could be a problem. Try changing the name of the table
or field and see if the problem persists.

Carl Rapson

Ok this is what I get. Compile Error: Method or data member not found. and
this is what i put in the AfterUpdate box of the Category combobox.

[quoted text clipped - 23 lines]

helps
any. Thanks

<snip>

 

[Carl Rapson]

Yes, it clarifies that the table doesn't have the same name as a field
within the table, so that rules out the second possibility. What about the
control names?

Carl Rapson

My record source for the Descriptions subform is SELECT [Descriptions].
Quantity, [Descriptions].UnitPrice, [Descriptions].Description,
[Descriptions]
Taxable, [Descriptions].Total, [Descriptions].Invoice FROM Descriptions;
does this help any? Thanks

What is the name of the control on your form that is bound to the
Description field? That's the control name you'll need to use in this
statement:

Me.txtDescription = rs.Fields![Description]

I used 'txtDescription' because I don't know the names of your controls.
The
same applies to the other fields.

Another possibility: You have a field named Description in a table named
Description, and that could be a problem. Try changing the name of the
table
or field and see if the problem persists.

Carl Rapson

Ok this is what I get. Compile Error: Method or data member not found.
and
this is what i put in the AfterUpdate box of the Category combobox.

[quoted text clipped - 23 lines]

helps
any. Thanks

<snip>

 

[stephendeloach via AccessMonster.com]

Ok I have been playing with it and I have changed it a little. The record
source of the Descriptions subform is SELECT Descriptions.Quantity,
Descriptions.UnitPrice, Descriptions.Description, Descriptions.Taxable,
Descriptions.Total, Descriptions.Invoice, Descriptions.Category FROM
Descriptions;

Control names... Quantity - Quantity, UnitPrice - UnitPrice, is that what you
are looking for?

Also, my Main table and Descriptions table are linked by the category fields.
Im trying to give you any info that might help... Thanks for the fast reply..

Yes, it clarifies that the table doesn't have the same name as a field
within the table, so that rules out the second possibility. What about the
control names?

Carl Rapson

My record source for the Descriptions subform is SELECT [Descriptions].
Quantity, [Descriptions].UnitPrice, [Descriptions].Description,

[quoted text clipped - 27 lines]

 

[Carl Rapson]

So the controls have the same names as the table fields? Normally, that's
not considered a good thing, because sometimes Access (and you yourself) can
get confused as to whether you're referring to the field or the control.
It's common to name controls with a prefix that specifies the type of
control - 'cbo' for combo box, for example, or 'txt' for text box.

But anyway, you should be using those actual control names in your
assignment statements:

Me.UnitPrice = rs.Fields![UnitPrice]

If the error still occurs, what is the exact VBA code where the error is
occurring?

Carl Rapson

Ok I have been playing with it and I have changed it a little. The record
source of the Descriptions subform is SELECT Descriptions.Quantity,
Descriptions.UnitPrice, Descriptions.Description, Descriptions.Taxable,
Descriptions.Total, Descriptions.Invoice, Descriptions.Category FROM
Descriptions;

Control names... Quantity - Quantity, UnitPrice - UnitPrice, is that what
you
are looking for?

Also, my Main table and Descriptions table are linked by the category
fields.
Im trying to give you any info that might help... Thanks for the fast
reply..

Yes, it clarifies that the table doesn't have the same name as a field
within the table, so that rules out the second possibility. What about the
control names?

Carl Rapson

My record source for the Descriptions subform is SELECT [Descriptions].
Quantity, [Descriptions].UnitPrice, [Descriptions].Description,

[quoted text clipped - 27 lines]

<snip>

 

[stephendeloach via AccessMonster.com]

I dont even know what im doing now... this is what I have

Private Sub Combo20_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Main WHERE Combo20=" & Me.
Combo20)
If Not rs.EOF Then
Me.txtDescription = rs.Fields![Description]
Me.txtUnitPrice = rs.Fields![UnitPrice]
Me.TxtTotal = rs.Fields![Total]
Me.txtQuantity = rs.Fields![Quantity]
Me.txtTaxable = rs.Fields![Taxable]
Me.txtCategory = rs.Fields![Category]

End Sub

I get a Compile Error: Method or data member not round. and it goes an
highlights Private Sub Combo20_AfterUpdate() in yellow and highlights
txtDescription in blue... any suggestions?? Thanks

Stephen

So the controls have the same names as the table fields? Normally, that's
not considered a good thing, because sometimes Access (and you yourself) can
get confused as to whether you're referring to the field or the control.
It's common to name controls with a prefix that specifies the type of
control - 'cbo' for combo box, for example, or 'txt' for text box.

But anyway, you should be using those actual control names in your
assignment statements:

Me.UnitPrice = rs.Fields![UnitPrice]

If the error still occurs, what is the exact VBA code where the error is
occurring?

Carl Rapson

Ok I have been playing with it and I have changed it a little. The record
source of the Descriptions subform is SELECT Descriptions.Quantity,

[quoted text clipped - 22 lines]

 

[Carl Rapson]

Is there a textbox control on your form named 'txtDescription? If not, what
is the name of the control that corresponds to the Description field on your
form? To find the name, click on the textbox control and open the Properties
window and click on the Other tab. The Name property should be the first one
on that tab. That is the name you should be using in the assignment
statement:

Me.<value in the Name property> = rs.Fields![Description]

Also, is there a field named Combo20 in your Main table? If not, you are
using the wrong name in your Select statement. This should be

...WHERE <field name>=" & Me.Combo20

Carl Rapson

I dont even know what im doing now... this is what I have

Private Sub Combo20_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Main WHERE Combo20=" & Me.
Combo20)
If Not rs.EOF Then
Me.txtDescription = rs.Fields![Description]
Me.txtUnitPrice = rs.Fields![UnitPrice]
Me.TxtTotal = rs.Fields![Total]
Me.txtQuantity = rs.Fields![Quantity]
Me.txtTaxable = rs.Fields![Taxable]
Me.txtCategory = rs.Fields![Category]

End Sub

I get a Compile Error: Method or data member not round. and it goes an
highlights Private Sub Combo20_AfterUpdate() in yellow and highlights
txtDescription in blue... any suggestions?? Thanks

Stephen

So the controls have the same names as the table fields? Normally, that's
not considered a good thing, because sometimes Access (and you yourself)
can
get confused as to whether you're referring to the field or the control.
It's common to name controls with a prefix that specifies the type of
control - 'cbo' for combo box, for example, or 'txt' for text box.

But anyway, you should be using those actual control names in your
assignment statements:

Me.UnitPrice = rs.Fields![UnitPrice]

If the error still occurs, what is the exact VBA code where the error is
occurring?

Carl Rapson

Ok I have been playing with it and I have changed it a little. The
record
source of the Descriptions subform is SELECT Descriptions.Quantity,

[quoted text clipped - 22 lines]

<snip>

 

[stephendeloach via AccessMonster.com]

Private Sub Combo20_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Main WHERE Combo20=" & Me.
Combo20)
If Not rs.EOF Then
Me.Description = rs.Fields![Description]
Me.UnitPrice = rs.Fields![UnitPrice]
Me.Total = rs.Fields![Total]
Me.Quantity = rs.Fields![Quantity]
Me.Taxable = rs.Fields![Taxable]
Me.Category = rs.Fields![Category]

End Sub

is what I have now.. I still get the compile error: and Private Sub.. is in
yellow and UnitPrice is in blue. The combo box I am choosing the category
from is Name-Combo20 and ControlSource-Category Thanks

Is there a textbox control on your form named 'txtDescription? If not, what
is the name of the control that corresponds to the Description field on your
form? To find the name, click on the textbox control and open the Properties
window and click on the Other tab. The Name property should be the first one
on that tab. That is the name you should be using in the assignment
statement:

Me.<value in the Name property> = rs.Fields![Description]

Also, is there a field named Combo20 in your Main table? If not, you are
using the wrong name in your Select statement. This should be

...WHERE <field name>=" & Me.Combo20

Carl Rapson

I dont even know what im doing now... this is what I have

[quoted text clipped - 41 lines]

 

[Carl Rapson]

First of all, you're missing the End If statement, which should be just
before the End Sub statement.

If the field in the table is named Category, then that is what should be in
your SELECT statement:

"SELECT * FROM Main WHERE Category=" & Combo20

I can't see why you're getting the compile error, based on what you've
posted. If the form is bound to the Main table and there is a control on the
form bound to the UnitPrice field, your assignment statement should work.

Carl Rapson

Private Sub Combo20_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Main WHERE Combo20=" & Me.
Combo20)
If Not rs.EOF Then
Me.Description = rs.Fields![Description]
Me.UnitPrice = rs.Fields![UnitPrice]
Me.Total = rs.Fields![Total]
Me.Quantity = rs.Fields![Quantity]
Me.Taxable = rs.Fields![Taxable]
Me.Category = rs.Fields![Category]

End Sub

is what I have now.. I still get the compile error: and Private Sub.. is
in
yellow and UnitPrice is in blue. The combo box I am choosing the
category
from is Name-Combo20 and ControlSource-Category Thanks

Is there a textbox control on your form named 'txtDescription? If not,
what
is the name of the control that corresponds to the Description field on
your
form? To find the name, click on the textbox control and open the
Properties
window and click on the Other tab. The Name property should be the first
one
on that tab. That is the name you should be using in the assignment
statement:

Me.<value in the Name property> = rs.Fields![Description]

Also, is there a field named Combo20 in your Main table? If not, you are
using the wrong name in your Select statement. This should be

...WHERE <field name>=" & Me.Combo20

Carl Rapson

I dont even know what im doing now... this is what I have

[quoted text clipped - 41 lines]

<snip>

 

[stephendeloach via AccessMonster.com]

I have deleted some stuff just to see what it would do... this is what i have
now..

Private Sub Combo20_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Main WHERE Combo20=" & Me.
Combo20)
If Not rs.EOF Then
Me.Description = rs.Fields![Description]
End If
End Sub

I get a Runtime error 3061: Too few parameters. expected 1.
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Main WHERE Combo20=" & Me.
Combo20)
is in yellow.

When i change Combo20 to Category, I get Runtime error 3464. Data type
mismatch in criteria expression.

?

First of all, you're missing the End If statement, which should be just
before the End Sub statement.

If the field in the table is named Category, then that is what should be in
your SELECT statement:

"SELECT * FROM Main WHERE Category=" & Combo20

I can't see why you're getting the compile error, based on what you've
posted. If the form is bound to the Main table and there is a control on the
form bound to the UnitPrice field, your assignment statement should work.

Carl Rapson

Private Sub Combo20_AfterUpdate()
Dim rs As DAO.Recordset

[quoted text clipped - 41 lines]

 

[Carl Rapson]

Your first error (3061) probably occurs because there isn't a field named
Combo20 in your table. Isn't that field named Category? What is the data
type of the Category field in the Main table? If it's text, you'll need to
put quotes around the value in your SELECT statement:

"SELECT * FROM Main WHERE Category='" & Me.Combo20 & "'"

Here's what the quotes look like with extra spaces inserted for clarity:

...Category= ' " & Me.Combo20 & " ' "

Text values have to be surrounded by quotes in a SELECT statement. That
could be the source of the 3464 error.

Carl Rapson

I have deleted some stuff just to see what it would do... this is what i
have
now..

Private Sub Combo20_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Main WHERE Combo20=" & Me.
Combo20)
If Not rs.EOF Then
Me.Description = rs.Fields![Description]
End If
End Sub

I get a Runtime error 3061: Too few parameters. expected 1.
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Main WHERE Combo20=" & Me.
Combo20)
is in yellow.

When i change Combo20 to Category, I get Runtime error 3464. Data type
mismatch in criteria expression.

?

First of all, you're missing the End If statement, which should be just
before the End Sub statement.

If the field in the table is named Category, then that is what should be
in
your SELECT statement:

"SELECT * FROM Main WHERE Category=" & Combo20

I can't see why you're getting the compile error, based on what you've
posted. If the form is bound to the Main table and there is a control on
the
form bound to the UnitPrice field, your assignment statement should work.

Carl Rapson

Private Sub Combo20_AfterUpdate()
Dim rs As DAO.Recordset

[quoted text clipped - 41 lines]

<snip>

 

[stephendeloach via AccessMonster.com]

Private Sub Combo20_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Main WHERE Category='" & Me.
Combo20 & "'")
If Not rs.EOF Then
Me.Description = rs.Fields![Description]
Me.Quantity = rs.Fields![Quantity]
Me.Total = rs.Fields![Total]
Me.Invoice = rs.Fields![Invoice]
Me.Category = rs.Fields![Category]

End If
End Sub

Thats what I got. When I take out Me.Quantity = rs.Fields![Quantity] through
Me.Category... it dosent come up with an error. when I add them in it comes
up with Compile error. method or data member not found. ? They are set to
Numbers dose that? matter?

Your first error (3061) probably occurs because there isn't a field named
Combo20 in your table. Isn't that field named Category? What is the data
type of the Category field in the Main table? If it's text, you'll need to
put quotes around the value in your SELECT statement:

"SELECT * FROM Main WHERE Category='" & Me.Combo20 & "'"

Here's what the quotes look like with extra spaces inserted for clarity:

...Category= ' " & Me.Combo20 & " ' "

Text values have to be surrounded by quotes in a SELECT statement. That
could be the source of the 3464 error.

Carl Rapson

I have deleted some stuff just to see what it would do... this is what i
have

[quoted text clipped - 40 lines]

 

[Carl Rapson]

You say that when you remove those lines it doesn't come up with the error.
Is the value of the Description field from the recordset actually placed
into the Description field on the form when you do that? If so, then the
only thing I can think of is, once again, that maybe the field and/or
control names aren't correct. What are the names of the controls on the form
corresponding to each of those fields?

Data types shouldn't matter. The field/control name issue is all I can think
of based on what you've given me.

Carl Rapson

Private Sub Combo20_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Main WHERE Category='" &
Me.
Combo20 & "'")
If Not rs.EOF Then
Me.Description = rs.Fields![Description]
Me.Quantity = rs.Fields![Quantity]
Me.Total = rs.Fields![Total]
Me.Invoice = rs.Fields![Invoice]
Me.Category = rs.Fields![Category]

End If
End Sub

Thats what I got. When I take out Me.Quantity = rs.Fields![Quantity]
through
Me.Category... it dosent come up with an error. when I add them in it
comes
up with Compile error. method or data member not found. ? They are set
to
Numbers dose that? matter?

Your first error (3061) probably occurs because there isn't a field named
Combo20 in your table. Isn't that field named Category? What is the data
type of the Category field in the Main table? If it's text, you'll need to
put quotes around the value in your SELECT statement:

"SELECT * FROM Main WHERE Category='" & Me.Combo20 & "'"

Here's what the quotes look like with extra spaces inserted for clarity:

...Category= ' " & Me.Combo20 & " ' "

Text values have to be surrounded by quotes in a SELECT statement. That
could be the source of the 3464 error.

Carl Rapson

I have deleted some stuff just to see what it would do... this is what i
have

[quoted text clipped - 40 lines]

<snip>

 

[stephendeloach via AccessMonster.com]

I changed the controlsource different than the name.. maybe this will help?
The names are..
Name ControlSource
Quantity 1Quantity
description 1description
unitprice 1unitprice
taxable 1taxable
total 1total
invoice 1invoice
category 1category


im thinking i dont have it setup right! thanks for your time

You say that when you remove those lines it doesn't come up with the error.
Is the value of the Description field from the recordset actually placed
into the Description field on the form when you do that? If so, then the
only thing I can think of is, once again, that maybe the field and/or
control names aren't correct. What are the names of the controls on the form
corresponding to each of those fields?

Data types shouldn't matter. The field/control name issue is all I can think
of based on what you've given me.

Carl Rapson

Private Sub Combo20_AfterUpdate()
Dim rs As DAO.Recordset

[quoted text clipped - 40 lines]