List Box

S

SG

I have a frmreferb form and frmrefurbsub form.

the sub form and the following field Supplier - Product - Product Desc -
Serial Number

I want to be able to click on the Supplier List Box and the click on the
product list box but I only want products to display which are available
from the supplier I have selected initially.

Does anyone have any ideas how I can achieve the above?

Thank you in advance.

S
 
M

Marshall Barton

SG said:
Sorry I ment Combo boxes and NOT List Boxes in my post!
Click to expand...


Doesn't matter, the solution to both is the same ;-)

Set the product combo box's RowSource to a query that has a
criteria like this for the supplier field:
Forms!frmreferb.frmrefurbsub.Form.cboSupplier

Then, use a little code in the Supplier combo box's
AfterUpdate event procedure:

Me.cboProduct = Null
Me.cboProduct.Requery
 
S

SG

Umm,,

I seem to be really struggling with this one....

Does it make any difference if I have the form in datasheet view and is a
subform?

All that seems to happen when I do the below is a select the supplier and I
get a runtime error and then there is nothing in the product combo list??

Do you have any detialed documentation I can follow?

Thank you

S
 
M

Marshall Barton

There are other explanations in many other newsgroup posts
that you can find by searching the Google archives.

You should have posted the error message that was displayed
because it is usually a valuable clue. However, I suspect
that your error was a type mismatch. If so, it would imply
that there is something funny in the way you have set up the
combo boxes.

Please post a Copy/Paste of both combo box's RowSource SQL
statements so I can see the fields. I also need to see both
combo box's Name, ColumnCount and BoundColumn properties and
a Copy/Paste of the actual code that you used.
 
S

SG

Marsh

I have removed the original code etc so I now have an frmrefurb and
frmrefurbsubform. Here are the details of the combo boxes - Thank you for
you help!


The subform is where I have the combo boxes in datasheet view I have
referbdetailID Supplier Name: ProductID and Product Description Name:

In deisgn view I have CBOSupplierName Row Source SELECT DISTINCT
tblProducts.SupplierID, tblSuppliers.SupplierName FROM tblSuppliers INNER
JOIN tblProducts ON tblSuppliers.SupplierID=tblProducts.SupplierID; Column
Count 2 Bound Column 2 Control Source SupplierName



Then I Have CBOProduct Row Source SELECT tblProducts.ProductID,
tblProducts.ProductName FROM tblProducts; Column Count 2 Bound Column 1
Control Source ProductID




and finally CBOProductDescription Row Source SELECT
tblProducts.ProductDescription FROM tblProducts; Column Count 1 Bound Column
1 Control Source ProductDescription.



Marshall Barton said:
There are other explanations in many other newsgroup posts
that you can find by searching the Google archives.

You should have posted the error message that was displayed
because it is usually a valuable clue. However, I suspect
that your error was a type mismatch. If so, it would imply
that there is something funny in the way you have set up the
combo boxes.

Please post a Copy/Paste of both combo box's RowSource SQL
statements so I can see the fields. I also need to see both
combo box's Name, ColumnCount and BoundColumn properties and
a Copy/Paste of the actual code that you used.
--
Marsh
MVP [MS Access]

Does it make any difference if I have the form in datasheet view and is a
subform?

All that seems to happen when I do the below is a select the supplier and
I
get a runtime error and then there is nothing in the product combo list??

Do you have any detialed documentation I can follow?
Click to expand...
Click to expand...
 
M

Marshall Barton

It appears that the refurbdetail TABLE is a list of products
associated with a refurb.

In a situation like that, unless you are doing something
weird with the product description, tblreferbdetail only
needs two fields:
referbdetailID (Autonumber? primary key)
ProductID (Long Foreign key)

You can include SupplierID in there if you think it will
help somewhere else, but a query can get the SupplierID from
the product table using the ProductID. OTTH, you do not
want the product name, description or serial number fields
in tblreferbdetail as they would duplicate the information
in the product table.

The table could, of course, have other fields for data
specific to the refurbdetail (e.g. refubdate, work estimate.
etc).

Given all that, the subform would have (at least):

txtreferbdetailID
Visible No
ControlSource referbdetailID

CBOSupplier:
Row Source:
SELECT SupplierID, SupplierName
FROM tblSuppliers
Column Count 2
Bound Column 1
ColumnWidths 0;
Control Source SupplierID

CBOProduct:
Row Source:
SELECT ProductID, ProductName,
ProductDescription, ProductSerialNum
FROM tblProducts
WHERE SupplierID =
Forms!frmrefurb.frmrefurbsubform.Form.CBOSupplier
Column Count 4
Bound Column 1
ColumnWidths 0;;0;0
Control Source ProductID

txtProductDescr:
ControlSource =CBOProduct.Column(2)

txtSerialNum
ControlSource =CBOProduct.Column(3)
--
Marsh
MVP [MS Access]

I have removed the original code etc so I now have an frmrefurb and
frmrefurbsubform. Here are the details of the combo boxes

The subform is where I have the combo boxes in datasheet view I have
Click to expand...
referbdetailID
Supplier Name
ProductID
Product Description Name
CBOSupplierName
Click to expand...
Row Source:
SELECT DISTINCT tblProducts.SupplierID,
tblSuppliers.SupplierName
FROM tblSuppliers INNER JOIN tblProducts
ON tblSuppliers.SupplierID=tblProducts.SupplierID;
Column Count 2
Bound Column 2
Control Source SupplierName
CBOProduct
Click to expand...
Row Source:
SELECT tblProducts.ProductID, tblProducts.ProductName
FROM tblProducts;
Column Count 2
Bound Column 1
Control Source ProductID
CBOProductDescription
Click to expand...
Row Source:
SELECT tblProducts.ProductDescription
FROM tblProducts;
Column Count 1
Bound Column 1
Control Source ProductDescription
 
S

SG

I have done what you have suggested and have two issues which is most like
down to something I have done When I access the page whihc may I add has a
tab control I get the following message Enter Value
Forms!frmrefurb.tblReferbDetails Subform.Form.CBOSupplier ??

The second issue I have is I can select the supplier and then try and select
the product whihc displays the correct product but will not allow me to
select it? No message appears?

Any Ideas?


Marshall Barton said:
It appears that the refurbdetail TABLE is a list of products
associated with a refurb.

In a situation like that, unless you are doing something
weird with the product description, tblreferbdetail only
needs two fields:
referbdetailID (Autonumber? primary key)
ProductID (Long Foreign key)

You can include SupplierID in there if you think it will
help somewhere else, but a query can get the SupplierID from
the product table using the ProductID. OTTH, you do not
want the product name, description or serial number fields
in tblreferbdetail as they would duplicate the information
in the product table.

The table could, of course, have other fields for data
specific to the refurbdetail (e.g. refubdate, work estimate.
etc).

Given all that, the subform would have (at least):

txtreferbdetailID
Visible No
ControlSource referbdetailID

CBOSupplier:
Row Source:
SELECT SupplierID, SupplierName
FROM tblSuppliers
Column Count 2
Bound Column 1
ColumnWidths 0;
Control Source SupplierID

CBOProduct:
Row Source:
SELECT ProductID, ProductName,
ProductDescription, ProductSerialNum
FROM tblProducts
WHERE SupplierID =
Forms!frmrefurb.frmrefurbsubform.Form.CBOSupplier
Column Count 4
Bound Column 1
ColumnWidths 0;;0;0
Control Source ProductID

txtProductDescr:
ControlSource =CBOProduct.Column(2)

txtSerialNum
ControlSource =CBOProduct.Column(3)
--
Marsh
MVP [MS Access]

I have removed the original code etc so I now have an frmrefurb and
frmrefurbsubform. Here are the details of the combo boxes

The subform is where I have the combo boxes in datasheet view I have
Click to expand...
referbdetailID
Supplier Name
ProductID
Product Description Name
CBOSupplierName
Click to expand...
Row Source:
SELECT DISTINCT tblProducts.SupplierID,
tblSuppliers.SupplierName
FROM tblSuppliers INNER JOIN tblProducts
ON tblSuppliers.SupplierID=tblProducts.SupplierID;
Column Count 2
Bound Column 2
Control Source SupplierName
CBOProduct
Click to expand...
Row Source:
SELECT tblProducts.ProductID, tblProducts.ProductName
FROM tblProducts;
Column Count 2
Bound Column 1
Control Source ProductID
CBOProductDescription
Click to expand...
Row Source:
SELECT tblProducts.ProductDescription
FROM tblProducts;
Column Count 1
Bound Column 1
Control Source ProductDescription
"Marshall Barton" wrote
Click to expand...
Click to expand...
 
M

Marshall Barton

Are you sure that the subform **control** is named
tblReferbDetails Subform
Even if that is the control's name, it would require [ ]
around it because of the space.
Forms!frmrefurb.[tblReferbDetails Subform].Form.CBOSupplier

If the combo box won't let you select an entry in the list,
I think it might be because its control source is not
updatable. Either the revord source query is not updatable,
the combo box is bound to something funny in the subform's
record source or you might have an = sign in front of the
field name or ???. If you can't spot the problem, please
post details about the combo box so I have some clues to
work with.

Do the text box's display the description and serial number?

Did you remember the two lines of code in the supplier combo
box's AfterUpdate event procedure?
 
S

SG

Marsh,


I have checked the names of the forms and change the referbsub form the be
frmReferbDetailsSubform but I still get the "Enter Parameter Value"
MessageForms!frmReferb.frmReferbDetailsSubform.Form.CBOSupplier when I open
the frmroom main form??

If I cancel the above message and go to the frmreferb form I have a few
entries which appear on the frmreferbdetailssubform but no products.

If I try and select a Supplier Name I get the following Visual Basic Error
Run-time Error '-2147352567 (8000200009)': You tried to assign the null
value to a variable that is not a variant data type. Which I presume is this
piece of code Me.CBOProduct = Null behind the afterupdate event of the
cbosupplier control. If I remove this piece of code and attempt the above
again and select the supplier I get the "Enter Parameter Value"
MessageForms!frmReferb.frmReferbDetailsSubform.Form.CBOSupplier Message. If
I select Cancel and the try and select a product nothing is in the list BUT
if I type in number 3 in the "Enter Parameter Value"
MessageForms!frmReferb.frmReferbDetailsSubform.Form.CBOSupplier Message I
get a number 3 in the supplier and then try and select a product I get the 2
products listed which are available through that supplier which is correct I
can select this and save it. But if I type in an entry which does not appear
on the form when I recieve this message "Enter Parameter Value"
MessageForms!frmReferb.frmReferbDetailsSubform.Form.CBOSupplier Message all
the product fields which apeeared correct on the screen go blank.



The Code in the CBOSupplier AfterUpdate Event is

Private Sub CBOSupplier_AfterUpdate()
Me.CBOProduct = Null
Me.CBOProduct.Requery

End Sub


|The desctiption and serial number appear when I am able to select a product
although what I would also like to do is select a supplier which would then
list product catagories eg wallpaper, curtains, bedcover and then allow me
to select a type/description as there could be many different descriptions
for the wallpaper.

Once again thank you for your assistance...!

Marshall Barton said:
Are you sure that the subform **control** is named
tblReferbDetails Subform
Even if that is the control's name, it would require [ ]
around it because of the space.
Forms!frmrefurb.[tblReferbDetails Subform].Form.CBOSupplier

If the combo box won't let you select an entry in the list,
I think it might be because its control source is not
updatable. Either the revord source query is not updatable,
the combo box is bound to something funny in the subform's
record source or you might have an = sign in front of the
field name or ???. If you can't spot the problem, please
post details about the combo box so I have some clues to
work with.

Do the text box's display the description and serial number?

Did you remember the two lines of code in the supplier combo
box's AfterUpdate event procedure?
--
Marsh
MVP [MS Access]

I have done what you have suggested and have two issues which is most like
down to something I have done When I access the page whihc may I add has a
tab control I get the following message Enter Value
Forms!frmrefurb.tblReferbDetails Subform.Form.CBOSupplier ??

The second issue I have is I can select the supplier and then try and
select
the product whihc displays the correct product but will not allow me to
select it? No message appears?


"Marshall Barton" wrote
Click to expand...
Click to expand...
 
M

Marshall Barton

Comments inline below.
--
Marsh
MVP [MS Access]

I have checked the names of the forms and change the referbsub form the be
frmReferbDetailsSubform but I still get the "Enter Parameter Value"
MessageForms!frmReferb.frmReferbDetailsSubform.Form.CBOSupplier when I open
the frmroom main form??
Click to expand...

We really need to use precise language here.
When you say:
"checked the names of the forms and change the
referbsub form the be frmReferbDetailsSubform"
it sounds to me as if you checked/changed the name of the
form object in the database window. I have been trying to
emphasize that the name of the form object used as a subform
is **not** the name under discussion. You need to use the
name of the subform **control** on the main form. (The name
of the form object that the subform control displays is in
the control's SourceObject property and is not used in the
query's reference.)

The main form name in the query's combo box reference is the
name of the form object. You have said the name of the main
form object was frmReferb, but above you refer to the main
form as frmroom. If frmReferb is not the main form, change
the frmReferb to the name of the form object in the database
window.

The general idea of referring to a control in a form used as
a subform is:

Forms!name_of_main_form_object.name_of_subform_control.Form.name_of_combobox

If I cancel the above message and go to the frmreferb form I have a few
entries which appear on the frmreferbdetailssubform but no products.

If I try and select a Supplier Name I get the following Visual Basic Error
Run-time Error '-2147352567 (8000200009)': You tried to assign the null
value to a variable that is not a variant data type. Which I presume is this
piece of code Me.CBOProduct = Null behind the afterupdate event of the
cbosupplier control.
Click to expand...

That message implies that CBOProduct is declared as a VBA
variable in the main form's module. Double check to make
sure that CBOProduct is the name of the combo box and that
the name is not used in a DIM, Private, Public or Static
statement in the form's module. If CBOProduct is not
declared in the module, then I can not explain that error
message other than to say the form is probably corrupted.

If I remove this piece of code and attempt the above
again and select the supplier I get the "Enter Parameter Value"
MessageForms!frmReferb.frmReferbDetailsSubform.Form.CBOSupplier Message. If
I select Cancel and the try and select a product nothing is in the list BUT
if I type in number 3 in the "Enter Parameter Value"
MessageForms!frmReferb.frmReferbDetailsSubform.Form.CBOSupplier Message I
get a number 3 in the supplier and then try and select a product I get the 2
products listed which are available through that supplier which is correct I
can select this and save it. But if I type in an entry which does not appear
on the form when I recieve this message "Enter Parameter Value"
MessageForms!frmReferb.frmReferbDetailsSubform.Form.CBOSupplier Message all
the product fields which apeeared correct on the screen go blank.
Click to expand...

That is the normal behavior for entering a parameter value.
The problem we're trying to deal with is the incorrect
name(s?) in the reference.
The Code in the CBOSupplier AfterUpdate Event is

Private Sub CBOSupplier_AfterUpdate()
Me.CBOProduct = Null
Me.CBOProduct.Requery
End Sub

|The desctiption and serial number appear when I am able to select a product
Click to expand...

It's nice to know that something works as intended ;-)

although what I would also like to do is select a supplier which would then
list product catagories eg wallpaper, curtains, bedcover and then allow me
to select a type/description as there could be many different descriptions
for the wallpaper.
Click to expand...

Please, let's get what we have working before launching off
on enhancements. Once we get things working with two combo
boxes, you can use the same idea to insert a third combo
box.
 
T

tina

FWIW, you can go to http://home.att.net/~california.db/instructions.html,
click on the SubformControlName link, and download a PDF file that
illustrates what Marshall has been telling you about identifying the correct
name to use in a reference to a subform.

hth


SG said:
Marsh,


I have checked the names of the forms and change the referbsub form the be
frmReferbDetailsSubform but I still get the "Enter Parameter Value"
MessageForms!frmReferb.frmReferbDetailsSubform.Form.CBOSupplier when I open
the frmroom main form??

If I cancel the above message and go to the frmreferb form I have a few
entries which appear on the frmreferbdetailssubform but no products.

If I try and select a Supplier Name I get the following Visual Basic Error
Run-time Error '-2147352567 (8000200009)': You tried to assign the null
value to a variable that is not a variant data type. Which I presume is this
piece of code Me.CBOProduct = Null behind the afterupdate event of the
cbosupplier control. If I remove this piece of code and attempt the above
again and select the supplier I get the "Enter Parameter Value"
MessageForms!frmReferb.frmReferbDetailsSubform.Form.CBOSupplier Message. If
I select Cancel and the try and select a product nothing is in the list BUT
if I type in number 3 in the "Enter Parameter Value"
MessageForms!frmReferb.frmReferbDetailsSubform.Form.CBOSupplier Message I
get a number 3 in the supplier and then try and select a product I get the 2
products listed which are available through that supplier which is correct I
can select this and save it. But if I type in an entry which does not appear
on the form when I recieve this message "Enter Parameter Value"
MessageForms!frmReferb.frmReferbDetailsSubform.Form.CBOSupplier Message all
the product fields which apeeared correct on the screen go blank.



The Code in the CBOSupplier AfterUpdate Event is

Private Sub CBOSupplier_AfterUpdate()
Me.CBOProduct = Null
Me.CBOProduct.Requery

End Sub


|The desctiption and serial number appear when I am able to select a product
although what I would also like to do is select a supplier which would then
list product catagories eg wallpaper, curtains, bedcover and then allow me
to select a type/description as there could be many different descriptions
for the wallpaper.

Once again thank you for your assistance...!

Marshall Barton said:
Are you sure that the subform **control** is named
tblReferbDetails Subform
Even if that is the control's name, it would require [ ]
around it because of the space.
Forms!frmrefurb.[tblReferbDetails Subform].Form.CBOSupplier

If the combo box won't let you select an entry in the list,
I think it might be because its control source is not
updatable. Either the revord source query is not updatable,
the combo box is bound to something funny in the subform's
record source or you might have an = sign in front of the
field name or ???. If you can't spot the problem, please
post details about the combo box so I have some clues to
work with.

Do the text box's display the description and serial number?

Did you remember the two lines of code in the supplier combo
box's AfterUpdate event procedure?
--
Marsh
MVP [MS Access]

I have done what you have suggested and have two issues which is most like
down to something I have done When I access the page whihc may I add has a
tab control I get the following message Enter Value
Forms!frmrefurb.tblReferbDetails Subform.Form.CBOSupplier ??

The second issue I have is I can select the supplier and then try and
select
the product whihc displays the correct product but will not allow me to
select it? No message appears?


It appears that the refurbdetail TABLE is a list of products
associated with a refurb.

In a situation like that, unless you are doing something
weird with the product description, tblreferbdetail only
needs two fields:
referbdetailID (Autonumber? primary key)
ProductID (Long Foreign key)

You can include SupplierID in there if you think it will
help somewhere else, but a query can get the SupplierID from
the product table using the ProductID. OTTH, you do not
want the product name, description or serial number fields
in tblreferbdetail as they would duplicate the information
in the product table.

The table could, of course, have other fields for data
specific to the refurbdetail (e.g. refubdate, work estimate.
etc).

Given all that, the subform would have (at least):

txtreferbdetailID
Visible No
ControlSource referbdetailID

CBOSupplier:
Row Source:
SELECT SupplierID, SupplierName
FROM tblSuppliers
Column Count 2
Bound Column 1
ColumnWidths 0;
Control Source SupplierID

CBOProduct:
Row Source:
SELECT ProductID, ProductName,
ProductDescription, ProductSerialNum
FROM tblProducts
WHERE SupplierID =
Forms!frmrefurb.frmrefurbsubform.Form.CBOSupplier
Column Count 4
Bound Column 1
ColumnWidths 0;;0;0
Control Source ProductID

txtProductDescr:
ControlSource =CBOProduct.Column(2)

txtSerialNum
ControlSource =CBOProduct.Column(3)


SG wrote:
I have removed the original code etc so I now have an frmrefurb and
frmrefurbsubform. Here are the details of the combo boxes

The subform is where I have the combo boxes in datasheet view I have
referbdetailID
Supplier Name
ProductID
Product Description Name

CBOSupplierName
Row Source:
SELECT DISTINCT tblProducts.SupplierID,
tblSuppliers.SupplierName
FROM tblSuppliers INNER JOIN tblProducts
ON tblSuppliers.SupplierID=tblProducts.SupplierID;
Column Count 2
Bound Column 2
Control Source SupplierName

CBOProduct
Row Source:
SELECT tblProducts.ProductID, tblProducts.ProductName
FROM tblProducts;
Column Count 2
Bound Column 1
Control Source ProductID

CBOProductDescription
Row Source:
SELECT tblProducts.ProductDescription
FROM tblProducts;
Column Count 1
Bound Column 1
Control Source ProductDescription


"Marshall Barton" wrote
There are other explanations in many other newsgroup posts
that you can find by searching the Google archives.

You should have posted the error message that was displayed
because it is usually a valuable clue. However, I suspect
that your error was a type mismatch. If so, it would imply
that there is something funny in the way you have set up the
combo boxes.

Please post a Copy/Paste of both combo box's RowSource SQL
statements so I can see the fields. I also need to see both
combo box's Name, ColumnCount and BoundColumn properties and
a Copy/Paste of the actual code that you used.


SG wrote:
Does it make any difference if I have the form in datasheet view and
is
a
subform?

All that seems to happen when I do the below is a select the supplier
and
I
get a runtime error and then there is nothing in the product combo
list??

Do you have any detialed documentation I can follow?


SG wrote:
I have a frmreferb form and frmrefurbsub form.

the sub form and the following field Supplier - Product - Product
Desc -
Serial Number

I want to be able to click on the Supplier List Box and the click
on
the
product list box but I only want products to display which are
available
from the supplier I have selected initially.


Set the product combo box's RowSource to a query that has a
criteria like this for the supplier field:
Forms!frmreferb.frmrefurbsub.Form.cboSupplier

Then, use a little code in the Supplier combo box's
AfterUpdate event procedure:

Me.cboProduct = Null
Me.cboProduct.Requery
Click to expand...
Click to expand...
Click to expand...
 
M

Marshall Barton

tina said:
FWIW, you can go to http://home.att.net/~california.db/instructions.html,
click on the SubformControlName link, and download a PDF file that
illustrates what Marshall has been telling you about identifying the correct
name to use in a reference to a subform.
Click to expand...


Tina, that's a very nice set of guidelines/explanations.
Thanks for posting the link, it explains things far more
clearly than I can manage in a post.

I didn't go through every page in detail, but I saw more
than enough to see that's all very well done. However, I
did not see anything that identifies the author, is it you
or do you know who put it together?
 
S

SG

Marsh...


Ahhhhhh!!!

I have finally woken up and got the form names correct. The issue I still
have though is the Run-Time error '-2147352567 (800200009)': You tried to
assign the Null Value to a variable that is not a variant data type.

I have completely scrapped the old forms and started the forms again in case
it was corrupt. I have checked the name of the control and even changed it
to CBOProducts but still I have the same error?

Marshall I wouldn't normally ask but this is the last piece of the puzzle,
are you or someone else able to look at this I do not mind donating
something or paying s fee if nessacery?!!
Kind regards

S
 
M

Marshall Barton

SG said:
Ahhhhhh!!!

I have finally woken up and got the form names correct. The issue I still
have though is the Run-Time error '-2147352567 (800200009)': You tried to
assign the Null Value to a variable that is not a variant data type.

I have completely scrapped the old forms and started the forms again in case
it was corrupt. I have checked the name of the control and even changed it
to CBOProducts but still I have the same error?

Marshall I wouldn't normally ask but this is the last piece of the puzzle,
are you or someone else able to look at this I do not mind donating
something or paying s fee if nessacery?!!
Click to expand...


No fees here, this is a free peer to peer assistance forum.
I will look at it in a pinch, but it would be better if you
could track down the code with the variable that's causing
the error. Besides, there's no telling when I might find a
big enough block of time to go through an unfamiliar
application.

It sure would be handy if the error message provided the
Debug option. Lacking that, scan through all the modules
for a variable declaration with the same name as a control
or field in the form. If you can't spot the culprit, try
placing a breakpoint at the start of each procedure in the
form and then single stepping through each line until the
error occurs.
 
T

tina

yes, be it ever so humble, it is my own. ;)
i put together those instructions for the things that i have a hard time
explaining in text, where a few pictures make it so much easier.
unfortunately, a number of the links go to a file that just says,
essentially, "not done yet". i don't know when i'll get to them - it's been
nearly two years now - and i'm beginning to wonder if it's worth doing,
since A2007 came along and i'm guessing the menus and perhaps general format
of the interface are different from prior versions.

but, at any rate, thanks for your kind words, Marsh <bows and smiles> and
i'm glad the link was useful in this case!
 
S

SG

Marsh,

Believe it or not I am still trying to resolve the problem I am having. I
have look through the entire database code and all but I am unable to locate
any incorrect name etc. The problem only occurs when I add this code Private
Sub CBOSupplier_AfterUpdate()


Me.CBOProduct = Null
Me.CBOProduct.Requery

To the after update event. If I remove the Me.CBOProduct = Null I no longer
recieve the error when I select a supplier from the datasheet I am also ble
to select a relevant product which the selected supplier supplies. BUT if I
then select another supplier on the same datasheet but a newline the
products go blank for the previous selections - strange?? if I save this and
go back in to the datasheet they display correctly.

Any further ideas / help??

Is there another way I can go about trying to achieve this?

Kind Regards


S


End Sub
 
M

Marshall Barton

SG said:
Believe it or not I am still trying to resolve the problem I am having. I
have look through the entire database code and all but I am unable to locate
any incorrect name etc. The problem only occurs when I add this code Private
Sub CBOSupplier_AfterUpdate()

Me.CBOProduct = Null
Me.CBOProduct.Requery

To the after update event. If I remove the Me.CBOProduct = Null I no longer
recieve the error when I select a supplier from the datasheet I am also ble
to select a relevant product which the selected supplier supplies. BUT if I
then select another supplier on the same datasheet but a newline the
products go blank for the previous selections - strange?? if I save this and
go back in to the datasheet they display correctly.
Click to expand...


What happens if you comment out the line:
Me.CBOProduct = Null
Does everything work?

The error message is what's throwing me here. It explicitly
states that CBOProduct is a VBA variable, not a control on
the form. The only way I can see that syntax being
interpreted as a VBA variable is if you have a statement
like:
Public CBOProduct As ???
at the top of the form's module.

All that is at least partially contradicted by the next line
Me.CBOProduct.Requery
where Me.CBOProduct is clearly interpreted as a control, not
a variable.

Access might be using some fancy footwork to interpret the
two lines differently, but I can not explain it. The only
fallback response I have to that kind of inconsistent
behavior is to blame it on some kind of code project
corruption. If that's the case, make a copy of the front
end database and decompile it.
http://www.granite.ab.ca/access/decompile.htm
http://www.trigeminal.com/usenet/usenet004.asp


If Decompile actually does something that resolved your
problem, then you need to try to prevent it from happening
again. Unfortunately, if the causes of corruption can be
well defined, then the cause would be removed. There are a
couple of semi common threads running through the fabric of
code corruption. The easiest to avoid is the
NameAutoCorrect feature by making sure it is turned off in
Tools - Options.

The other common cause is to edit a form/report module when
the form/report is open in any view other than design view.
So make sure you always switch to design view before making
any change to a form/report's code module.
 
S

SG

Marshall,

So I have scrapped the form completly and started afresh.

All is now working apart from one strange thing which happens. Let me
explain.....

When I us the Combo (Supplier) and select the supplier and then use the
Combo (Product) to select the product it diplays the correct products from
the selected supplier.
If I then select a diferent supplier for the next record on the datasheet
the previous records product goes blank, I then go on to select the product
forthe new record which
again offers the correct products for the chosen supplier but the previous
records product remains blank, why is this?
If I then for the next record select the same supplier as the first the
product in the first record reappears but the second record which has a
different supplier name disappears and so on.

Any ideas?

Thank yiou again for all of your help!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Setting up a PO Form 2
Automatically generating a series of reports 2
two questions: table question and Is there away to link an outsidedocument to access 2
help with access 2007 , may be filter problem 2
Form and Subform - Filter? Query? Help! 2
multi criteria code 2
link to microsoft word 4
populate form text fields depending on combo box selection 1

Top