I need help on this please.

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi Guys,

Things has gotten a little more complacated for me. here
it goes:

I have this form, frmReceiveables bounded to
tblReceivable, it contains:

PartNumber, ProductName, ProductDescription, JobNumber
(this comes from a dropdown menue based ona query, based
on my Jobs tables, all the items received are charged to
one job number which is the one for the shop), UnitsIn
(unit of particular item), UnitPrice, Note.

I have made another table, tblInventory and a form
bounded to this form, I want to be able to either type
the part number or choose it from a dropdown menu and the
other feilds as above to be pupolated to avoide
repeatation. Once these items filled in based on the
frmReceivable above , then I go and calculate the total
amount that received items cost. How do I do this auto
pupolation of the feilds on the frmInventory?


thank you in advance for your help.

Mike.
 
You probably should not have copies of ProductName and ProductDescription in
tblReceivable - that's a bad design unless you're allowing the name and
description in tblInventory to change over time and you want to capture the
values that were in tblInventory when you created the receivable record. To
display the related ProductName and ProductDescription, include the
tblInventory table in the query for the form. Create locked text boxes to
display the related values from tblInventory in your form. When the user
picks a new part number, Access will "autolookup" the related values to
display.

If you must have duplicate fields in tblReceivable, then do this:

Use a combo box for PartNumber. Give the combo box a name: cmbPartNumber.
For the Row Source, use something like:

SELECT PartNumber, ProductName, ProductDescription FROM tblInventory ORDER
BY PartNumber

Set the Bound Column to 1, Column Count to 3, and Column Widths to something
like 0.5"; 1.25"; 1.5"

In After Update of the combo box, execute this code:
Me.ProductName = Me.cmbPartNumber.Column(1)
Me.ProductDescription = Me.cmbPartNumber.Column(2)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
John,

You have explained it reversed. tblReceiceable and
frmReceive are the first step for our shop to order
something In. It has:

PartNumber, Productdescription, supplier, unitsIn,
unitPrice and a calculated field that calculates the
total price. Then, They go and enter data into the
Inventory table using frmInventory (has all the fields as
frmReceivable plus some extra ones), here we want to
eliminate the repeatation for typing and I wanted to have
so, on typing or selecting a partNumber the rest of the
fields which were on the frmReceive fill in automatically.

I have used the code below, but I get runtime error 2002,
saying you have canceled perivoius operation:

Private Sub ProductID_Exit(Cancel As Integer)
Dim varProductName, varProductDescription As Variant
'Dim varProductName, varProductDescription, Supplier,
UnitsIn, DateReceived, UnitPrice As Variant
varProductName = DLookup
("ProductName", "tblProductsIn", "ProductID =
[ProductID] ")
varProductDescription = DLookup
("ProducDescription", "tblProductsIn", "ProductID =
[ProductID] ")
varSupplier = DLookup
("Supplier", "tblProductsIn", "ProductID = [ProductID] ")
varUnitsIn = DLookup
("UnitsIn", "tblProductsIn", "ProductID = [ProductID] ")
varDateReceived = DLookup
("DateReceived", "tblProductsIn", "ProductID =
[ProductID] ")
varUnitPrice = DLookup
("UnitPrice", "tblProductsIn", "ProductID = [ProductID] ")

If (Not IsNull(varProductName)) Then Me![ProductName]
= varProductName
If (Not IsNull(varProductDescription)) Then Me!
[ProductDescription] = varProductDescription
If (Not IsNull(varSupplier)) Then Me![Supplier] =
varSupplier
If (Not IsNull(varUnitsIn)) Then Me![UnitsIn] =
varUnitsIn
If (Not IsNull(varDateReceived)) Then Me![DateReceived]
= varDateReceived
If (Not IsNull(varUnitPrice)) Then Me![UnitPrice] =
varUnitPrice

End Sub

well, the code seeoms to be alright, but I don't know why
its not working.

Thx,

Mike
 
Mike-

The only reason I can think that you're getting the Cancel is if you're
assigning Null or invalid values to some of the fields that have Required =
Yes or validation rules.

But you don't need all this code. First, Exit is the wrong event. You
should be doing this in AfterUpdate of the ProductID. If you're using a
combo box for ProductID, you can include all the other columns you need
(ProductName, ProductDescription, Supplier, etc.) in the Row Source of the
combo - even if you don't display them (Column Width = 0). You can fetch
the values via the Column(n) property of the combo box - with n=0 as the
first column.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Mike said:
John,

You have explained it reversed. tblReceiceable and
frmReceive are the first step for our shop to order
something In. It has:

PartNumber, Productdescription, supplier, unitsIn,
unitPrice and a calculated field that calculates the
total price. Then, They go and enter data into the
Inventory table using frmInventory (has all the fields as
frmReceivable plus some extra ones), here we want to
eliminate the repeatation for typing and I wanted to have
so, on typing or selecting a partNumber the rest of the
fields which were on the frmReceive fill in automatically.

I have used the code below, but I get runtime error 2002,
saying you have canceled perivoius operation:

Private Sub ProductID_Exit(Cancel As Integer)
Dim varProductName, varProductDescription As Variant
'Dim varProductName, varProductDescription, Supplier,
UnitsIn, DateReceived, UnitPrice As Variant
varProductName = DLookup
("ProductName", "tblProductsIn", "ProductID =
[ProductID] ")
varProductDescription = DLookup
("ProducDescription", "tblProductsIn", "ProductID =
[ProductID] ")
varSupplier = DLookup
("Supplier", "tblProductsIn", "ProductID = [ProductID] ")
varUnitsIn = DLookup
("UnitsIn", "tblProductsIn", "ProductID = [ProductID] ")
varDateReceived = DLookup
("DateReceived", "tblProductsIn", "ProductID =
[ProductID] ")
varUnitPrice = DLookup
("UnitPrice", "tblProductsIn", "ProductID = [ProductID] ")

If (Not IsNull(varProductName)) Then Me![ProductName]
= varProductName
If (Not IsNull(varProductDescription)) Then Me!
[ProductDescription] = varProductDescription
If (Not IsNull(varSupplier)) Then Me![Supplier] =
varSupplier
If (Not IsNull(varUnitsIn)) Then Me![UnitsIn] =
varUnitsIn
If (Not IsNull(varDateReceived)) Then Me![DateReceived]
= varDateReceived
If (Not IsNull(varUnitPrice)) Then Me![UnitPrice] =
varUnitPrice

End Sub

well, the code seeoms to be alright, but I don't know why
its not working.

Thx,

Mike
-----Original Message-----
You probably should not have copies of ProductName and ProductDescription in
tblReceivable - that's a bad design unless you're allowing the name and
description in tblInventory to change over time and you want to capture the
values that were in tblInventory when you created the receivable record. To
display the related ProductName and ProductDescription, include the
tblInventory table in the query for the form. Create locked text boxes to
display the related values from tblInventory in your form. When the user
picks a new part number, Access will "autolookup" the related values to
display.

If you must have duplicate fields in tblReceivable, then do this:

Use a combo box for PartNumber. Give the combo box a name: cmbPartNumber.
For the Row Source, use something like:

SELECT PartNumber, ProductName, ProductDescription FROM tblInventory ORDER
BY PartNumber

Set the Bound Column to 1, Column Count to 3, and Column Widths to something
like 0.5"; 1.25"; 1.5"

In After Update of the combo box, execute this code:
Me.ProductName = Me.cmbPartNumber.Column(1)
Me.ProductDescription = Me.cmbPartNumber.Column(2)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411



.
Click to expand...
Click to expand...
 
John,

I made this combo box based on a query based on the
productsIn table and placed it on my Inventory form. when
I click the combo box I do see all the field that I
choosed and when I select one, It changes the ProductID
value on my form accordingly. Now my question is how can
I get the form populated and save data in my tblInventory?

Thank you in advance for your valuable help.

Mike
-----Original Message-----
Mike-

The only reason I can think that you're getting the Cancel is if you're
assigning Null or invalid values to some of the fields that have Required =
Yes or validation rules.

But you don't need all this code. First, Exit is the wrong event. You
should be doing this in AfterUpdate of the ProductID. If you're using a
combo box for ProductID, you can include all the other columns you need
(ProductName, ProductDescription, Supplier, etc.) in the Row Source of the
combo - even if you don't display them (Column Width = 0). You can fetch
the values via the Column(n) property of the combo box - with n=0 as the
first column.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
Mike said:
John,

You have explained it reversed. tblReceiceable and
frmReceive are the first step for our shop to order
something In. It has:

PartNumber, Productdescription, supplier, unitsIn,
unitPrice and a calculated field that calculates the
total price. Then, They go and enter data into the
Inventory table using frmInventory (has all the fields as
frmReceivable plus some extra ones), here we want to
eliminate the repeatation for typing and I wanted to have
so, on typing or selecting a partNumber the rest of the
fields which were on the frmReceive fill in automatically.

I have used the code below, but I get runtime error 2002,
saying you have canceled perivoius operation:

Private Sub ProductID_Exit(Cancel As Integer)
Dim varProductName, varProductDescription As Variant
'Dim varProductName, varProductDescription, Supplier,
UnitsIn, DateReceived, UnitPrice As Variant
varProductName = DLookup
("ProductName", "tblProductsIn", "ProductID =
[ProductID] ")
varProductDescription = DLookup
("ProducDescription", "tblProductsIn", "ProductID =
[ProductID] ")
varSupplier = DLookup
("Supplier", "tblProductsIn", "ProductID = [ProductID] ")
varUnitsIn = DLookup
("UnitsIn", "tblProductsIn", "ProductID = [ProductID] ")
varDateReceived = DLookup
("DateReceived", "tblProductsIn", "ProductID =
[ProductID] ")
varUnitPrice = DLookup
("UnitPrice", "tblProductsIn", "ProductID = [ProductID] ")

If (Not IsNull(varProductName)) Then Me! [ProductName]
= varProductName
If (Not IsNull(varProductDescription)) Then Me!
[ProductDescription] = varProductDescription
If (Not IsNull(varSupplier)) Then Me![Supplier] =
varSupplier
If (Not IsNull(varUnitsIn)) Then Me![UnitsIn] =
varUnitsIn
If (Not IsNull(varDateReceived)) Then Me! [DateReceived]
= varDateReceived
If (Not IsNull(varUnitPrice)) Then Me![UnitPrice] =
varUnitPrice

End Sub

well, the code seeoms to be alright, but I don't know why
its not working.

Thx,

Mike
-----Original Message-----
You probably should not have copies of ProductName and ProductDescription in
tblReceivable - that's a bad design unless you're allowing the name and
description in tblInventory to change over time and
Click to expand...
you
want to capture the
values that were in tblInventory when you created the receivable record. To
display the related ProductName and
Click to expand...
ProductDescription,
include the
tblInventory table in the query for the form. Create locked text boxes to
display the related values from tblInventory in your form. When the user
picks a new part number, Access will "autolookup" the related values to
display.

If you must have duplicate fields in tblReceivable,
Click to expand...
then
do this:
Use a combo box for PartNumber. Give the combo box a name: cmbPartNumber.
For the Row Source, use something like:

SELECT PartNumber, ProductName, ProductDescription
Click to expand...
FROM
tblInventory ORDER
BY PartNumber

Set the Bound Column to 1, Column Count to 3, and
Click to expand...
Column
Widths to something
like 0.5"; 1.25"; 1.5"

In After Update of the combo box, execute this code:
Me.ProductName = Me.cmbPartNumber.Column(1)
Me.ProductDescription = Me.cmbPartNumber.Column(2)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
Hi Guys,

Things has gotten a little more complacated for me. here
it goes:

I have this form, frmReceiveables bounded to
tblReceivable, it contains:

PartNumber, ProductName, ProductDescription, JobNumber
(this comes from a dropdown menue based ona query, based
on my Jobs tables, all the items received are
Click to expand...
charged
to
one job number which is the one for the shop), UnitsIn
(unit of particular item), UnitPrice, Note.

I have made another table, tblInventory and a form
bounded to this form, I want to be able to either type
the part number or choose it from a dropdown menu
Click to expand...
and
the
other feilds as above to be pupolated to avoide
repeatation. Once these items filled in based on the
frmReceivable above , then I go and calculate the total
amount that received items cost. How do I do this auto
pupolation of the feilds on the frmInventory?


thank you in advance for your help.

Mike.



.
Click to expand...
Click to expand...


.
Click to expand...
 
John,

Codes are giving me problems and I decided to make it
simpler and go with the ComboBox on my Inventory form
bounded to the table tblProduct. I have created the combo
based on my tblProductsIn and the followings in the
AfterUpdate Property:

Private Sub cmbPartNumber_AfterUpdate()
Me.ProductName = Me.CmbPartNumber.Column(1)
Me.Supplier = Me.CmbPartNumber.Column(2)
Me.ProductDescription = Me.CmbPartNumber.Column(3)
Me.UnitsIn = Me.CmbPartNumber.Column(4)
Me.DateReceived = Me.CmbPartNumber.Column(5)
Me.UnitPrice = Me.CmbPartNumber.Column(6)
End Sub

I get this Run-Time Error Message:

Object doesn't support this property or methode and run-
time error number 438.

I also tried it with a query based on the tblProductsIn
but same answer, the query works and shows correct data,
as well and the combo shows correct data, it only won't
run with the above run-time error.

I would appreciate your input/thoughts on this.
Thx,

Mike
-----Original Message-----
Mike-

The only reason I can think that you're getting the Cancel is if you're
assigning Null or invalid values to some of the fields that have Required =
Yes or validation rules.

But you don't need all this code. First, Exit is the wrong event. You
should be doing this in AfterUpdate of the ProductID. If you're using a
combo box for ProductID, you can include all the other columns you need
(ProductName, ProductDescription, Supplier, etc.) in the Row Source of the
combo - even if you don't display them (Column Width = 0). You can fetch
the values via the Column(n) property of the combo box - with n=0 as the
first column.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
Mike said:
John,

You have explained it reversed. tblReceiceable and
frmReceive are the first step for our shop to order
something In. It has:

PartNumber, Productdescription, supplier, unitsIn,
unitPrice and a calculated field that calculates the
total price. Then, They go and enter data into the
Inventory table using frmInventory (has all the fields as
frmReceivable plus some extra ones), here we want to
eliminate the repeatation for typing and I wanted to have
so, on typing or selecting a partNumber the rest of the
fields which were on the frmReceive fill in automatically.

I have used the code below, but I get runtime error 2002,
saying you have canceled perivoius operation:

Private Sub ProductID_Exit(Cancel As Integer)
Dim varProductName, varProductDescription As Variant
'Dim varProductName, varProductDescription, Supplier,
UnitsIn, DateReceived, UnitPrice As Variant
varProductName = DLookup
("ProductName", "tblProductsIn", "ProductID =
[ProductID] ")
varProductDescription = DLookup
("ProducDescription", "tblProductsIn", "ProductID =
[ProductID] ")
varSupplier = DLookup
("Supplier", "tblProductsIn", "ProductID = [ProductID] ")
varUnitsIn = DLookup
("UnitsIn", "tblProductsIn", "ProductID = [ProductID] ")
varDateReceived = DLookup
("DateReceived", "tblProductsIn", "ProductID =
[ProductID] ")
varUnitPrice = DLookup
("UnitPrice", "tblProductsIn", "ProductID = [ProductID] ")

If (Not IsNull(varProductName)) Then Me! [ProductName]
= varProductName
If (Not IsNull(varProductDescription)) Then Me!
[ProductDescription] = varProductDescription
If (Not IsNull(varSupplier)) Then Me![Supplier] =
varSupplier
If (Not IsNull(varUnitsIn)) Then Me![UnitsIn] =
varUnitsIn
If (Not IsNull(varDateReceived)) Then Me! [DateReceived]
= varDateReceived
If (Not IsNull(varUnitPrice)) Then Me![UnitPrice] =
varUnitPrice

End Sub

well, the code seeoms to be alright, but I don't know why
its not working.

Thx,

Mike
-----Original Message-----
You probably should not have copies of ProductName and ProductDescription in
tblReceivable - that's a bad design unless you're allowing the name and
description in tblInventory to change over time and
Click to expand...
you
want to capture the
values that were in tblInventory when you created the receivable record. To
display the related ProductName and
Click to expand...
ProductDescription,
include the
tblInventory table in the query for the form. Create locked text boxes to
display the related values from tblInventory in your form. When the user
picks a new part number, Access will "autolookup" the related values to
display.

If you must have duplicate fields in tblReceivable,
Click to expand...
then
do this:
Use a combo box for PartNumber. Give the combo box a name: cmbPartNumber.
For the Row Source, use something like:

SELECT PartNumber, ProductName, ProductDescription
Click to expand...
FROM
tblInventory ORDER
BY PartNumber

Set the Bound Column to 1, Column Count to 3, and
Click to expand...
Column
Widths to something
like 0.5"; 1.25"; 1.5"

In After Update of the combo box, execute this code:
Me.ProductName = Me.cmbPartNumber.Column(1)
Me.ProductDescription = Me.cmbPartNumber.Column(2)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
Hi Guys,

Things has gotten a little more complacated for me. here
it goes:

I have this form, frmReceiveables bounded to
tblReceivable, it contains:

PartNumber, ProductName, ProductDescription, JobNumber
(this comes from a dropdown menue based ona query, based
on my Jobs tables, all the items received are
Click to expand...
charged
to
one job number which is the one for the shop), UnitsIn
(unit of particular item), UnitPrice, Note.

I have made another table, tblInventory and a form
bounded to this form, I want to be able to either type
the part number or choose it from a dropdown menu
Click to expand...
and
the
other feilds as above to be pupolated to avoide
repeatation. Once these items filled in based on the
frmReceivable above , then I go and calculate the total
amount that received items cost. How do I do this auto
pupolation of the feilds on the frmInventory?


thank you in advance for your help.

Mike.



.
Click to expand...
Click to expand...


.
Click to expand...
 
John,

I finally, was able to fetch the data I wanted on the
form based on ProductID in a combo box, but there is a
little problem in terms of saving data in the tblProduct
on which the from is bounded.

When I choose a record to be displayed, That Item only
gets saved in the table and replaced by the next item
choosed and displayed on the form. This way the table has
only one record in it and that is the record displyed on
the form. Where Have I gone wrong here?

Thanks,

Mike
-----Original Message-----
Mike-

The only reason I can think that you're getting the Cancel is if you're
assigning Null or invalid values to some of the fields that have Required =
Yes or validation rules.

But you don't need all this code. First, Exit is the wrong event. You
should be doing this in AfterUpdate of the ProductID. If you're using a
combo box for ProductID, you can include all the other columns you need
(ProductName, ProductDescription, Supplier, etc.) in the Row Source of the
combo - even if you don't display them (Column Width = 0). You can fetch
the values via the Column(n) property of the combo box - with n=0 as the
first column.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
Mike said:
John,

You have explained it reversed. tblReceiceable and
frmReceive are the first step for our shop to order
something In. It has:

PartNumber, Productdescription, supplier, unitsIn,
unitPrice and a calculated field that calculates the
total price. Then, They go and enter data into the
Inventory table using frmInventory (has all the fields as
frmReceivable plus some extra ones), here we want to
eliminate the repeatation for typing and I wanted to have
so, on typing or selecting a partNumber the rest of the
fields which were on the frmReceive fill in automatically.

I have used the code below, but I get runtime error 2002,
saying you have canceled perivoius operation:

Private Sub ProductID_Exit(Cancel As Integer)
Dim varProductName, varProductDescription As Variant
'Dim varProductName, varProductDescription, Supplier,
UnitsIn, DateReceived, UnitPrice As Variant
varProductName = DLookup
("ProductName", "tblProductsIn", "ProductID =
[ProductID] ")
varProductDescription = DLookup
("ProducDescription", "tblProductsIn", "ProductID =
[ProductID] ")
varSupplier = DLookup
("Supplier", "tblProductsIn", "ProductID = [ProductID] ")
varUnitsIn = DLookup
("UnitsIn", "tblProductsIn", "ProductID = [ProductID] ")
varDateReceived = DLookup
("DateReceived", "tblProductsIn", "ProductID =
[ProductID] ")
varUnitPrice = DLookup
("UnitPrice", "tblProductsIn", "ProductID = [ProductID] ")

If (Not IsNull(varProductName)) Then Me! [ProductName]
= varProductName
If (Not IsNull(varProductDescription)) Then Me!
[ProductDescription] = varProductDescription
If (Not IsNull(varSupplier)) Then Me![Supplier] =
varSupplier
If (Not IsNull(varUnitsIn)) Then Me![UnitsIn] =
varUnitsIn
If (Not IsNull(varDateReceived)) Then Me! [DateReceived]
= varDateReceived
If (Not IsNull(varUnitPrice)) Then Me![UnitPrice] =
varUnitPrice

End Sub

well, the code seeoms to be alright, but I don't know why
its not working.

Thx,

Mike
-----Original Message-----
You probably should not have copies of ProductName and ProductDescription in
tblReceivable - that's a bad design unless you're allowing the name and
description in tblInventory to change over time and
Click to expand...
you
want to capture the
values that were in tblInventory when you created the receivable record. To
display the related ProductName and
Click to expand...
ProductDescription,
include the
tblInventory table in the query for the form. Create locked text boxes to
display the related values from tblInventory in your form. When the user
picks a new part number, Access will "autolookup" the related values to
display.

If you must have duplicate fields in tblReceivable,
Click to expand...
then
do this:
Use a combo box for PartNumber. Give the combo box a name: cmbPartNumber.
For the Row Source, use something like:

SELECT PartNumber, ProductName, ProductDescription
Click to expand...
FROM
tblInventory ORDER
BY PartNumber

Set the Bound Column to 1, Column Count to 3, and
Click to expand...
Column
Widths to something
like 0.5"; 1.25"; 1.5"

In After Update of the combo box, execute this code:
Me.ProductName = Me.cmbPartNumber.Column(1)
Me.ProductDescription = Me.cmbPartNumber.Column(2)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
Hi Guys,

Things has gotten a little more complacated for me. here
it goes:

I have this form, frmReceiveables bounded to
tblReceivable, it contains:

PartNumber, ProductName, ProductDescription, JobNumber
(this comes from a dropdown menue based ona query, based
on my Jobs tables, all the items received are
Click to expand...
charged
to
one job number which is the one for the shop), UnitsIn
(unit of particular item), UnitPrice, Note.

I have made another table, tblInventory and a form
bounded to this form, I want to be able to either type
the part number or choose it from a dropdown menu
Click to expand...
and
the
other feilds as above to be pupolated to avoide
repeatation. Once these items filled in based on the
frmReceivable above , then I go and calculate the total
amount that received items cost. How do I do this auto
pupolation of the feilds on the frmInventory?


thank you in advance for your help.

Mike.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Apologies for the tardy reply.

From your original post, your form should have a Record Source of
tblInventory or a query based on this table. Your combo box row source
should be tblProducts, and the Control Source should be the ProductID in
tblInventory. You create new rows in tblInventory by going to a new row and
selecting a ProductID. Is this not what you're doing?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Mike said:
John,

I finally, was able to fetch the data I wanted on the
form based on ProductID in a combo box, but there is a
little problem in terms of saving data in the tblProduct
on which the from is bounded.

When I choose a record to be displayed, That Item only
gets saved in the table and replaced by the next item
choosed and displayed on the form. This way the table has
only one record in it and that is the record displyed on
the form. Where Have I gone wrong here?

Thanks,

Mike
-----Original Message-----
Mike-

The only reason I can think that you're getting the Cancel is if you're
assigning Null or invalid values to some of the fields that have Required =
Yes or validation rules.

But you don't need all this code. First, Exit is the wrong event. You
should be doing this in AfterUpdate of the ProductID. If you're using a
combo box for ProductID, you can include all the other columns you need
(ProductName, ProductDescription, Supplier, etc.) in the Row Source of the
combo - even if you don't display them (Column Width = 0). You can fetch
the values via the Column(n) property of the combo box - with n=0 as the
first column.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
Mike said:
John,

You have explained it reversed. tblReceiceable and
frmReceive are the first step for our shop to order
something In. It has:

PartNumber, Productdescription, supplier, unitsIn,
unitPrice and a calculated field that calculates the
total price. Then, They go and enter data into the
Inventory table using frmInventory (has all the fields as
frmReceivable plus some extra ones), here we want to
eliminate the repeatation for typing and I wanted to have
so, on typing or selecting a partNumber the rest of the
fields which were on the frmReceive fill in automatically.

I have used the code below, but I get runtime error 2002,
saying you have canceled perivoius operation:

Private Sub ProductID_Exit(Cancel As Integer)
Dim varProductName, varProductDescription As Variant
'Dim varProductName, varProductDescription, Supplier,
UnitsIn, DateReceived, UnitPrice As Variant
varProductName = DLookup
("ProductName", "tblProductsIn", "ProductID =
[ProductID] ")
varProductDescription = DLookup
("ProducDescription", "tblProductsIn", "ProductID =
[ProductID] ")
varSupplier = DLookup
("Supplier", "tblProductsIn", "ProductID = [ProductID] ")
varUnitsIn = DLookup
("UnitsIn", "tblProductsIn", "ProductID = [ProductID] ")
varDateReceived = DLookup
("DateReceived", "tblProductsIn", "ProductID =
[ProductID] ")
varUnitPrice = DLookup
("UnitPrice", "tblProductsIn", "ProductID = [ProductID] ")

If (Not IsNull(varProductName)) Then Me! [ProductName]
= varProductName
If (Not IsNull(varProductDescription)) Then Me!
[ProductDescription] = varProductDescription
If (Not IsNull(varSupplier)) Then Me![Supplier] =
varSupplier
If (Not IsNull(varUnitsIn)) Then Me![UnitsIn] =
varUnitsIn
If (Not IsNull(varDateReceived)) Then Me! [DateReceived]
= varDateReceived
If (Not IsNull(varUnitPrice)) Then Me![UnitPrice] =
varUnitPrice

End Sub

well, the code seeoms to be alright, but I don't know why
its not working.

Thx,

Mike
-----Original Message-----
You probably should not have copies of ProductName and
ProductDescription in
tblReceivable - that's a bad design unless you're
allowing the name and
description in tblInventory to change over time and you
want to capture the
values that were in tblInventory when you created the
receivable record. To
display the related ProductName and ProductDescription,
include the
tblInventory table in the query for the form. Create
locked text boxes to
display the related values from tblInventory in your
form. When the user
picks a new part number, Access will "autolookup" the
related values to
display.

If you must have duplicate fields in tblReceivable, then
do this:

Use a combo box for PartNumber. Give the combo box a
name: cmbPartNumber.
For the Row Source, use something like:

SELECT PartNumber, ProductName, ProductDescription FROM
tblInventory ORDER
BY PartNumber

Set the Bound Column to 1, Column Count to 3, and Column
Widths to something
like 0.5"; 1.25"; 1.5"

In After Update of the combo box, execute this code:
Me.ProductName = Me.cmbPartNumber.Column(1)
Me.ProductDescription = Me.cmbPartNumber.Column(2)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?
pg=personal&fr_id=1090&px=1434411
Hi Guys,

Things has gotten a little more complacated for me.
here
it goes:

I have this form, frmReceiveables bounded to
tblReceivable, it contains:

PartNumber, ProductName, ProductDescription, JobNumber
(this comes from a dropdown menue based ona query,
based
on my Jobs tables, all the items received are charged
to
one job number which is the one for the shop), UnitsIn
(unit of particular item), UnitPrice, Note.

I have made another table, tblInventory and a form
bounded to this form, I want to be able to either type
the part number or choose it from a dropdown menu and
the
other feilds as above to be pupolated to avoide
repeatation. Once these items filled in based on the
frmReceivable above , then I go and calculate the total
amount that received items cost. How do I do this auto
pupolation of the feilds on the frmInventory?


thank you in advance for your help.

Mike.



.
Click to expand...


.
Click to expand...
Click to expand...
 
John,

no, since I am trying to populate the form bounded to the
tblInventory in order to get records from tblProductsIn
and into tblInventory. I did this using the codes posted
by Dirk aand its working now. I still want to learn how
to create this combo box? as I mentioned, the combo i
made gets the data from the tblProductsIn using a query
based on the same table and get them onto a frmInventory
bounded to tblInventory.

Thanks for your attension and help.

Mike
-----Original Message-----
Apologies for the tardy reply.

From your original post, your form should have a Record Source of
tblInventory or a query based on this table. Your combo box row source
should be tblProducts, and the Control Source should be the ProductID in
tblInventory. You create new rows in tblInventory by going to a new row and
selecting a ProductID. Is this not what you're doing?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
Mike said:
John,

I finally, was able to fetch the data I wanted on the
form based on ProductID in a combo box, but there is a
little problem in terms of saving data in the tblProduct
on which the from is bounded.

When I choose a record to be displayed, That Item only
gets saved in the table and replaced by the next item
choosed and displayed on the form. This way the table has
only one record in it and that is the record displyed on
the form. Where Have I gone wrong here?

Thanks,

Mike
-----Original Message-----
Mike-

The only reason I can think that you're getting the Cancel is if you're
assigning Null or invalid values to some of the fields that have Required =
Yes or validation rules.

But you don't need all this code. First, Exit is the wrong event. You
should be doing this in AfterUpdate of the ProductID. If you're using a
combo box for ProductID, you can include all the other columns you need
(ProductName, ProductDescription, Supplier, etc.) in
Click to expand...
the
Row Source of the
combo - even if you don't display them (Column Width = 0). You can fetch
the values via the Column(n) property of the combo
Click to expand...
box -
with n=0 as the
first column.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
John,

You have explained it reversed. tblReceiceable and
frmReceive are the first step for our shop to order
something In. It has:

PartNumber, Productdescription, supplier, unitsIn,
unitPrice and a calculated field that calculates the
total price. Then, They go and enter data into the
Inventory table using frmInventory (has all the
Click to expand...
fields
as
frmReceivable plus some extra ones), here we want to
eliminate the repeatation for typing and I wanted to have
so, on typing or selecting a partNumber the rest of the
fields which were on the frmReceive fill in automatically.

I have used the code below, but I get runtime error 2002,
saying you have canceled perivoius operation:

Private Sub ProductID_Exit(Cancel As Integer)
Dim varProductName, varProductDescription As Variant
'Dim varProductName, varProductDescription, Supplier,
UnitsIn, DateReceived, UnitPrice As Variant
varProductName = DLookup
("ProductName", "tblProductsIn", "ProductID =
[ProductID] ")
varProductDescription = DLookup
("ProducDescription", "tblProductsIn", "ProductID =
[ProductID] ")
varSupplier = DLookup
("Supplier", "tblProductsIn", "ProductID = [ProductID] ")
varUnitsIn = DLookup
("UnitsIn", "tblProductsIn", "ProductID = [ProductID] ")
varDateReceived = DLookup
("DateReceived", "tblProductsIn", "ProductID =
[ProductID] ")
varUnitPrice = DLookup
("UnitPrice", "tblProductsIn", "ProductID = [ProductID] ")

If (Not IsNull(varProductName)) Then Me! [ProductName]
= varProductName
If (Not IsNull(varProductDescription)) Then Me!
[ProductDescription] = varProductDescription
If (Not IsNull(varSupplier)) Then Me![Supplier] =
varSupplier
If (Not IsNull(varUnitsIn)) Then Me![UnitsIn] =
varUnitsIn
If (Not IsNull(varDateReceived)) Then Me! [DateReceived]
= varDateReceived
If (Not IsNull(varUnitPrice)) Then Me![UnitPrice] =
varUnitPrice

End Sub

well, the code seeoms to be alright, but I don't
Click to expand...
know
why
its not working.

Thx,

Mike
-----Original Message-----
You probably should not have copies of ProductName and
ProductDescription in
tblReceivable - that's a bad design unless you're
allowing the name and
description in tblInventory to change over time and you
want to capture the
values that were in tblInventory when you created the
receivable record. To
display the related ProductName and ProductDescription,
include the
tblInventory table in the query for the form. Create
locked text boxes to
display the related values from tblInventory in your
form. When the user
picks a new part number, Access will "autolookup" the
related values to
display.

If you must have duplicate fields in tblReceivable, then
do this:

Use a combo box for PartNumber. Give the combo box a
name: cmbPartNumber.
For the Row Source, use something like:

SELECT PartNumber, ProductName, ProductDescription FROM
tblInventory ORDER
BY PartNumber

Set the Bound Column to 1, Column Count to 3, and Column
Widths to something
like 0.5"; 1.25"; 1.5"

In After Update of the combo box, execute this code:
Me.ProductName = Me.cmbPartNumber.Column(1)
Me.ProductDescription = Me.cmbPartNumber.Column (2)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?
pg=personal&fr_id=1090&px=1434411
Hi Guys,

Things has gotten a little more complacated for me.
here
it goes:

I have this form, frmReceiveables bounded to
tblReceivable, it contains:

PartNumber, ProductName, ProductDescription, JobNumber
(this comes from a dropdown menue based ona query,
based
on my Jobs tables, all the items received are charged
to
one job number which is the one for the shop), UnitsIn
(unit of particular item), UnitPrice, Note.

I have made another table, tblInventory and a form
bounded to this form, I want to be able to either type
the part number or choose it from a dropdown menu and
the
other feilds as above to be pupolated to avoide
repeatation. Once these items filled in based on the
frmReceivable above , then I go and calculate the total
amount that received items cost. How do I do this auto
pupolation of the feilds on the frmInventory?


thank you in advance for your help.

Mike.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Mike-

In an earlier post, you said:

Private Sub cmbPartNumber_AfterUpdate()
Me.ProductName = Me.CmbPartNumber.Column(1)
Me.Supplier = Me.CmbPartNumber.Column(2)
Me.ProductDescription = Me.CmbPartNumber.Column(3)
Me.UnitsIn = Me.CmbPartNumber.Column(4)
Me.DateReceived = Me.CmbPartNumber.Column(5)
Me.UnitPrice = Me.CmbPartNumber.Column(6)
End Sub

I get this Run-Time Error Message:

Object doesn't support this property or methode and run-
time error number 438.

This implies that the name of the combo box is "cmbPartNumber." However,
the code shows it as CmbPartNumber - which implies that Access can't find
this control. (VB always corrects capitalization to match the original
definition of the variable or object.) That would explain why you're
getting the 438 error when you try to reference the Column property. What
is the actual name of the combo box?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Mike said:
John,

no, since I am trying to populate the form bounded to the
tblInventory in order to get records from tblProductsIn
and into tblInventory. I did this using the codes posted
by Dirk aand its working now. I still want to learn how
to create this combo box? as I mentioned, the combo i
made gets the data from the tblProductsIn using a query
based on the same table and get them onto a frmInventory
bounded to tblInventory.

Thanks for your attension and help.

Mike
-----Original Message-----
Apologies for the tardy reply.

From your original post, your form should have a Record Source of
tblInventory or a query based on this table. Your combo box row source
should be tblProducts, and the Control Source should be the ProductID in
tblInventory. You create new rows in tblInventory by going to a new row and
selecting a ProductID. Is this not what you're doing?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
Mike said:
John,

I finally, was able to fetch the data I wanted on the
form based on ProductID in a combo box, but there is a
little problem in terms of saving data in the tblProduct
on which the from is bounded.

When I choose a record to be displayed, That Item only
gets saved in the table and replaced by the next item
choosed and displayed on the form. This way the table has
only one record in it and that is the record displyed on
the form. Where Have I gone wrong here?

Thanks,

Mike
-----Original Message-----
Mike-

The only reason I can think that you're getting the
Cancel is if you're
assigning Null or invalid values to some of the fields
that have Required =
Yes or validation rules.

But you don't need all this code. First, Exit is the
wrong event. You
should be doing this in AfterUpdate of the ProductID.
If you're using a
combo box for ProductID, you can include all the other
columns you need
(ProductName, ProductDescription, Supplier, etc.) in the
Row Source of the
combo - even if you don't display them (Column Width =
0). You can fetch
the values via the Column(n) property of the combo box -
with n=0 as the
first column.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?
pg=personal&fr_id=1090&px=1434411
John,

You have explained it reversed. tblReceiceable and
frmReceive are the first step for our shop to order
something In. It has:

PartNumber, Productdescription, supplier, unitsIn,
unitPrice and a calculated field that calculates the
total price. Then, They go and enter data into the
Inventory table using frmInventory (has all the fields
as
frmReceivable plus some extra ones), here we want to
eliminate the repeatation for typing and I wanted to
have
so, on typing or selecting a partNumber the rest of the
fields which were on the frmReceive fill in
automatically.

I have used the code below, but I get runtime error
2002,
saying you have canceled perivoius operation:

Private Sub ProductID_Exit(Cancel As Integer)
Dim varProductName, varProductDescription As Variant
'Dim varProductName, varProductDescription, Supplier,
UnitsIn, DateReceived, UnitPrice As Variant
varProductName = DLookup
("ProductName", "tblProductsIn", "ProductID =
[ProductID] ")
varProductDescription = DLookup
("ProducDescription", "tblProductsIn", "ProductID =
[ProductID] ")
varSupplier = DLookup
("Supplier", "tblProductsIn", "ProductID =
[ProductID] ")
varUnitsIn = DLookup
("UnitsIn", "tblProductsIn", "ProductID =
[ProductID] ")
varDateReceived = DLookup
("DateReceived", "tblProductsIn", "ProductID =
[ProductID] ")
varUnitPrice = DLookup
("UnitPrice", "tblProductsIn", "ProductID =
[ProductID] ")

If (Not IsNull(varProductName)) Then Me!
[ProductName]
= varProductName
If (Not IsNull(varProductDescription)) Then Me!
[ProductDescription] = varProductDescription
If (Not IsNull(varSupplier)) Then Me![Supplier] =
varSupplier
If (Not IsNull(varUnitsIn)) Then Me![UnitsIn] =
varUnitsIn
If (Not IsNull(varDateReceived)) Then Me!
[DateReceived]
= varDateReceived
If (Not IsNull(varUnitPrice)) Then Me![UnitPrice] =
varUnitPrice

End Sub

well, the code seeoms to be alright, but I don't know
why
its not working.

Thx,

Mike
-----Original Message-----
You probably should not have copies of ProductName and
ProductDescription in
tblReceivable - that's a bad design unless you're
allowing the name and
description in tblInventory to change over time and
you
want to capture the
values that were in tblInventory when you created the
receivable record. To
display the related ProductName and
ProductDescription,
include the
tblInventory table in the query for the form. Create
locked text boxes to
display the related values from tblInventory in your
form. When the user
picks a new part number, Access will "autolookup" the
related values to
display.

If you must have duplicate fields in tblReceivable,
then
do this:

Use a combo box for PartNumber. Give the combo box a
name: cmbPartNumber.
For the Row Source, use something like:

SELECT PartNumber, ProductName, ProductDescription
FROM
tblInventory ORDER
BY PartNumber

Set the Bound Column to 1, Column Count to 3, and
Column
Widths to something
like 0.5"; 1.25"; 1.5"

In After Update of the combo box, execute this code:
Me.ProductName = Me.cmbPartNumber.Column(1)
Me.ProductDescription = Me.cmbPartNumber.Column (2)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?
pg=personal&fr_id=1090&px=1434411
Hi Guys,

Things has gotten a little more complacated for me.
here
it goes:

I have this form, frmReceiveables bounded to
tblReceivable, it contains:

PartNumber, ProductName, ProductDescription,
JobNumber
(this comes from a dropdown menue based ona query,
based
on my Jobs tables, all the items received are
charged
to
one job number which is the one for the shop),
UnitsIn
(unit of particular item), UnitPrice, Note.

I have made another table, tblInventory and a form
bounded to this form, I want to be able to either
type
the part number or choose it from a dropdown menu
and
the
other feilds as above to be pupolated to avoide
repeatation. Once these items filled in based on the
frmReceivable above , then I go and calculate the
total
amount that received items cost. How do I do this
auto
pupolation of the feilds on the frmInventory?


thank you in advance for your help.

Mike.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
John,

The actual name is the one in the AfterUpdate Private Sub
heading, i.e. cmbPartNumber.

i think with what you said the problem is due to the
Capitalization and since in VB it changes to "C"
from "c", I will change the name to CmbPartNumber and try
it again. I also need to blame the keyboard on my Laptop
as well which is acting up these days.

Thanks,

Mike
-----Original Message-----
Mike-

In an earlier post, you said:

Private Sub cmbPartNumber_AfterUpdate()
Me.ProductName = Me.CmbPartNumber.Column(1)
Me.Supplier = Me.CmbPartNumber.Column(2)
Me.ProductDescription = Me.CmbPartNumber.Column(3)
Me.UnitsIn = Me.CmbPartNumber.Column(4)
Me.DateReceived = Me.CmbPartNumber.Column(5)
Me.UnitPrice = Me.CmbPartNumber.Column(6)
End Sub

I get this Run-Time Error Message:

Object doesn't support this property or methode and run-
time error number 438.

This implies that the name of the combo box is "cmbPartNumber." However,
the code shows it as CmbPartNumber - which implies that Access can't find
this control. (VB always corrects capitalization to match the original
definition of the variable or object.) That would explain why you're
getting the 438 error when you try to reference the Column property. What
is the actual name of the combo box?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
Mike said:
John,

no, since I am trying to populate the form bounded to the
tblInventory in order to get records from tblProductsIn
and into tblInventory. I did this using the codes posted
by Dirk aand its working now. I still want to learn how
to create this combo box? as I mentioned, the combo i
made gets the data from the tblProductsIn using a query
based on the same table and get them onto a frmInventory
bounded to tblInventory.

Thanks for your attension and help.

Mike
-----Original Message-----
Apologies for the tardy reply.

From your original post, your form should have a
Click to expand...
Record
Source of
tblInventory or a query based on this table. Your
Click to expand...
combo
box row source
should be tblProducts, and the Control Source should
Click to expand...
be
the ProductID in
tblInventory. You create new rows in tblInventory by going to a new row and
selecting a ProductID. Is this not what you're doing?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
John,

I finally, was able to fetch the data I wanted on the
form based on ProductID in a combo box, but there is a
little problem in terms of saving data in the tblProduct
on which the from is bounded.

When I choose a record to be displayed, That Item only
gets saved in the table and replaced by the next item
choosed and displayed on the form. This way the
Click to expand...
table
has
only one record in it and that is the record
Click to expand...
displyed
on
the form. Where Have I gone wrong here?

Thanks,

Mike
-----Original Message-----
Mike-

The only reason I can think that you're getting the
Cancel is if you're
assigning Null or invalid values to some of the fields
that have Required =
Yes or validation rules.

But you don't need all this code. First, Exit is the
wrong event. You
should be doing this in AfterUpdate of the ProductID.
If you're using a
combo box for ProductID, you can include all the other
columns you need
(ProductName, ProductDescription, Supplier, etc.)
Click to expand...
in
the
Row Source of the
combo - even if you don't display them (Column Width =
0). You can fetch
the values via the Column(n) property of the combo box -
with n=0 as the
first column.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?
pg=personal&fr_id=1090&px=1434411
John,

You have explained it reversed. tblReceiceable and
frmReceive are the first step for our shop to order
something In. It has:

PartNumber, Productdescription, supplier, unitsIn,
unitPrice and a calculated field that calculates the
total price. Then, They go and enter data into the
Inventory table using frmInventory (has all the fields
as
frmReceivable plus some extra ones), here we want to
eliminate the repeatation for typing and I wanted to
have
so, on typing or selecting a partNumber the rest
Click to expand...
of
the
fields which were on the frmReceive fill in
automatically.

I have used the code below, but I get runtime error
2002,
saying you have canceled perivoius operation:

Private Sub ProductID_Exit(Cancel As Integer)
Dim varProductName, varProductDescription As Variant
'Dim varProductName, varProductDescription, Supplier,
UnitsIn, DateReceived, UnitPrice As Variant
varProductName = DLookup
("ProductName", "tblProductsIn", "ProductID =
[ProductID] ")
varProductDescription = DLookup
("ProducDescription", "tblProductsIn", "ProductID =
[ProductID] ")
varSupplier = DLookup
("Supplier", "tblProductsIn", "ProductID =
[ProductID] ")
varUnitsIn = DLookup
("UnitsIn", "tblProductsIn", "ProductID =
[ProductID] ")
varDateReceived = DLookup
("DateReceived", "tblProductsIn", "ProductID =
[ProductID] ")
varUnitPrice = DLookup
("UnitPrice", "tblProductsIn", "ProductID =
[ProductID] ")

If (Not IsNull(varProductName)) Then Me!
[ProductName]
= varProductName
If (Not IsNull(varProductDescription)) Then Me!
[ProductDescription] = varProductDescription
If (Not IsNull(varSupplier)) Then Me![Supplier] =
varSupplier
If (Not IsNull(varUnitsIn)) Then Me![UnitsIn] =
varUnitsIn
If (Not IsNull(varDateReceived)) Then Me!
[DateReceived]
= varDateReceived
If (Not IsNull(varUnitPrice)) Then Me! [UnitPrice] =
varUnitPrice

End Sub

well, the code seeoms to be alright, but I don't know
why
its not working.

Thx,

Mike
-----Original Message-----
You probably should not have copies of
Click to expand...
ProductName
and
ProductDescription in
tblReceivable - that's a bad design unless you're
allowing the name and
description in tblInventory to change over time and
you
want to capture the
values that were in tblInventory when you
Click to expand...
created
the
receivable record. To
display the related ProductName and
ProductDescription,
include the
tblInventory table in the query for the form. Create
locked text boxes to
display the related values from tblInventory in your
form. When the user
picks a new part number, Access
Click to expand...
will "autolookup"
the
related values to
display.

If you must have duplicate fields in tblReceivable,
then
do this:

Use a combo box for PartNumber. Give the combo box a
name: cmbPartNumber.
For the Row Source, use something like:

SELECT PartNumber, ProductName, ProductDescription
FROM
tblInventory ORDER
BY PartNumber

Set the Bound Column to 1, Column Count to 3, and
Column
Widths to something
like 0.5"; 1.25"; 1.5"

In After Update of the combo box, execute this code:
Me.ProductName = Me.cmbPartNumber.Column(1)
Me.ProductDescription =
Click to expand...
Me.cmbPartNumber.Column
(2)
--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?
pg=personal&fr_id=1090&px=1434411
Hi Guys,

Things has gotten a little more complacated
Click to expand...
for
me.
here
it goes:

I have this form, frmReceiveables bounded to
tblReceivable, it contains:

PartNumber, ProductName, ProductDescription,
JobNumber
(this comes from a dropdown menue based ona query,
based
on my Jobs tables, all the items received are
charged
to
one job number which is the one for the shop),
UnitsIn
(unit of particular item), UnitPrice, Note.

I have made another table, tblInventory and a form
bounded to this form, I want to be able to either
type
the part number or choose it from a dropdown menu
and
the
other feilds as above to be pupolated to avoide
repeatation. Once these items filled in based
Click to expand...
on
the
frmReceivable above , then I go and calculate the
total
amount that received items cost. How do I do this
auto
pupolation of the feilds on the frmInventory?


thank you in advance for your help.

Mike.



.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Mike-

If VB is insisting on capitalizing it, then it must think that there's
something else named CmbPartNumber. If you have auto-list members turned
on, you should see the Column property when you type:

Me.cmbPartNumber.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Mike said:
John,

The actual name is the one in the AfterUpdate Private Sub
heading, i.e. cmbPartNumber.

i think with what you said the problem is due to the
Capitalization and since in VB it changes to "C"
from "c", I will change the name to CmbPartNumber and try
it again. I also need to blame the keyboard on my Laptop
as well which is acting up these days.

Thanks,

Mike
-----Original Message-----
Mike-

In an earlier post, you said:

Private Sub cmbPartNumber_AfterUpdate()
Me.ProductName = Me.CmbPartNumber.Column(1)
Me.Supplier = Me.CmbPartNumber.Column(2)
Me.ProductDescription = Me.CmbPartNumber.Column(3)
Me.UnitsIn = Me.CmbPartNumber.Column(4)
Me.DateReceived = Me.CmbPartNumber.Column(5)
Me.UnitPrice = Me.CmbPartNumber.Column(6)
End Sub

I get this Run-Time Error Message:

Object doesn't support this property or methode and run-
time error number 438.

This implies that the name of the combo box is "cmbPartNumber." However,
the code shows it as CmbPartNumber - which implies that Access can't find
this control. (VB always corrects capitalization to match the original
definition of the variable or object.) That would explain why you're
getting the 438 error when you try to reference the Column property. What
is the actual name of the combo box?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
Mike said:
John,

no, since I am trying to populate the form bounded to the
tblInventory in order to get records from tblProductsIn
and into tblInventory. I did this using the codes posted
by Dirk aand its working now. I still want to learn how
to create this combo box? as I mentioned, the combo i
made gets the data from the tblProductsIn using a query
based on the same table and get them onto a frmInventory
bounded to tblInventory.

Thanks for your attension and help.

Mike
-----Original Message-----
Apologies for the tardy reply.

From your original post, your form should have a Record
Source of
tblInventory or a query based on this table. Your combo
box row source
should be tblProducts, and the Control Source should be
the ProductID in
tblInventory. You create new rows in tblInventory by
going to a new row and
selecting a ProductID. Is this not what you're doing?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?
pg=personal&fr_id=1090&px=1434411
John,

I finally, was able to fetch the data I wanted on the
form based on ProductID in a combo box, but there is a
little problem in terms of saving data in the
tblProduct
on which the from is bounded.

When I choose a record to be displayed, That Item only
gets saved in the table and replaced by the next item
choosed and displayed on the form. This way the table
has
only one record in it and that is the record displyed
on
the form. Where Have I gone wrong here?

Thanks,

Mike
-----Original Message-----
Mike-

The only reason I can think that you're getting the
Cancel is if you're
assigning Null or invalid values to some of the fields
that have Required =
Yes or validation rules.

But you don't need all this code. First, Exit is the
wrong event. You
should be doing this in AfterUpdate of the ProductID.
If you're using a
combo box for ProductID, you can include all the other
columns you need
(ProductName, ProductDescription, Supplier, etc.) in
the
Row Source of the
combo - even if you don't display them (Column Width =
0). You can fetch
the values via the Column(n) property of the combo
box -
with n=0 as the
first column.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?
pg=personal&fr_id=1090&px=1434411
John,

You have explained it reversed. tblReceiceable and
frmReceive are the first step for our shop to order
something In. It has:

PartNumber, Productdescription, supplier, unitsIn,
unitPrice and a calculated field that calculates the
total price. Then, They go and enter data into the
Inventory table using frmInventory (has all the
fields
as
frmReceivable plus some extra ones), here we want to
eliminate the repeatation for typing and I wanted to
have
so, on typing or selecting a partNumber the rest of
the
fields which were on the frmReceive fill in
automatically.

I have used the code below, but I get runtime error
2002,
saying you have canceled perivoius operation:

Private Sub ProductID_Exit(Cancel As Integer)
Dim varProductName, varProductDescription As Variant
'Dim varProductName, varProductDescription,
Supplier,
UnitsIn, DateReceived, UnitPrice As Variant
varProductName = DLookup
("ProductName", "tblProductsIn", "ProductID =
[ProductID] ")
varProductDescription = DLookup
("ProducDescription", "tblProductsIn", "ProductID =
[ProductID] ")
varSupplier = DLookup
("Supplier", "tblProductsIn", "ProductID =
[ProductID] ")
varUnitsIn = DLookup
("UnitsIn", "tblProductsIn", "ProductID =
[ProductID] ")
varDateReceived = DLookup
("DateReceived", "tblProductsIn", "ProductID =
[ProductID] ")
varUnitPrice = DLookup
("UnitPrice", "tblProductsIn", "ProductID =
[ProductID] ")

If (Not IsNull(varProductName)) Then Me!
[ProductName]
= varProductName
If (Not IsNull(varProductDescription)) Then Me!
[ProductDescription] = varProductDescription
If (Not IsNull(varSupplier)) Then Me![Supplier] =
varSupplier
If (Not IsNull(varUnitsIn)) Then Me![UnitsIn] =
varUnitsIn
If (Not IsNull(varDateReceived)) Then Me!
[DateReceived]
= varDateReceived
If (Not IsNull(varUnitPrice)) Then Me! [UnitPrice] =
varUnitPrice

End Sub

well, the code seeoms to be alright, but I don't
know
why
its not working.

Thx,

Mike
-----Original Message-----
You probably should not have copies of ProductName
and
ProductDescription in
tblReceivable - that's a bad design unless you're
allowing the name and
description in tblInventory to change over time and
you
want to capture the
values that were in tblInventory when you created
the
receivable record. To
display the related ProductName and
ProductDescription,
include the
tblInventory table in the query for the form.
Create
locked text boxes to
display the related values from tblInventory in
your
form. When the user
picks a new part number, Access will "autolookup"
the
related values to
display.

If you must have duplicate fields in tblReceivable,
then
do this:

Use a combo box for PartNumber. Give the combo
box a
name: cmbPartNumber.
For the Row Source, use something like:

SELECT PartNumber, ProductName, ProductDescription
FROM
tblInventory ORDER
BY PartNumber

Set the Bound Column to 1, Column Count to 3, and
Column
Widths to something
like 0.5"; 1.25"; 1.5"

In After Update of the combo box, execute this
code:
Me.ProductName = Me.cmbPartNumber.Column(1)
Me.ProductDescription = Me.cmbPartNumber.Column
(2)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?
pg=personal&fr_id=1090&px=1434411
Hi Guys,

Things has gotten a little more complacated for
me.
here
it goes:

I have this form, frmReceiveables bounded to
tblReceivable, it contains:

PartNumber, ProductName, ProductDescription,
JobNumber
(this comes from a dropdown menue based ona
query,
based
on my Jobs tables, all the items received are
charged
to
one job number which is the one for the shop),
UnitsIn
(unit of particular item), UnitPrice, Note.

I have made another table, tblInventory and a
form
bounded to this form, I want to be able to either
type
the part number or choose it from a dropdown menu
and
the
other feilds as above to be pupolated to avoide
repeatation. Once these items filled in based on
the
frmReceivable above , then I go and calculate the
total
amount that received items cost. How do I do this
auto
pupolation of the feilds on the frmInventory?


thank you in advance for your help.

Mike.



.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top