Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms / November 2007

Tip: Looking for answers? Try searching our database.

List Box

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
SG - 04 Nov 2007 15:54 GMT
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
SG - 04 Nov 2007 15:58 GMT
Sorry I ment Combo boxes and NOT List Boxes in my  post!

>I have a frmreferb form and frmrefurbsub form.
>
[quoted text clipped - 10 lines]
>
> S
Marshall Barton - 04 Nov 2007 17:30 GMT
>Sorry I ment Combo boxes and NOT List Boxes in my  post!
>
[quoted text clipped - 6 lines]
>> product list box but I only want products to display which are available
>> from the supplier I have selected initially.

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

Signature

Marsh
MVP [MS Access]

SG - 04 Nov 2007 19:49 GMT
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

>>Sorry I ment Combo boxes and NOT List Boxes in my  post!
>>
[quoted text clipped - 18 lines]
> Me.cboProduct = Null
> Me.cboProduct.Requery
Marshall Barton - 04 Nov 2007 21:56 GMT
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.
Signature

Marsh
MVP [MS Access]

>Does it make any difference if I have the form in datasheet view and is a
>subform?
[quoted text clipped - 22 lines]
>> Me.cboProduct = Null
>> Me.cboProduct.Requery
SG - 04 Nov 2007 23:38 GMT
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.

> There are other explanations in many other newsgroup posts
> that you can find by searching the Google archives.
[quoted text clipped - 39 lines]
>>> Me.cboProduct = Null
>>> Me.cboProduct.Requery
Marshall Barton - 05 Nov 2007 17:36 GMT
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)
Signature

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
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

>> There are other explanations in many other newsgroup posts
>> that you can find by searching the Google archives.
[quoted text clipped - 40 lines]
>>>> Me.cboProduct = Null
>>>> Me.cboProduct.Requery
SG - 05 Nov 2007 23:41 GMT
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?

> It appears that the refurbdetail TABLE is a list of products
> associated with a refurb.
[quoted text clipped - 130 lines]
>>>>> Me.cboProduct = Null
>>>>> Me.cboProduct.Requery
Marshall Barton - 06 Nov 2007 05:21 GMT
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?
Signature

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
[quoted text clipped - 140 lines]
>>>>>> Me.cboProduct = Null
>>>>>> Me.cboProduct.Requery
SG - 06 Nov 2007 09:12 GMT
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...!

> Are you sure that the subform **control** is named
> tblReferbDetails Subform
[quoted text clipped - 162 lines]
>>>>>>> Me.cboProduct = Null
>>>>>>> Me.cboProduct.Requery
Marshall Barton - 06 Nov 2007 17:15 GMT
Comments inline below.
Signature

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??

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.
[quoted text clipped - 4 lines]
>piece of code  Me.CBOProduct = Null behind the afterupdate event of the
>cbosupplier control.

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"
[quoted text clipped - 8 lines]
>MessageForms!frmReferb.frmReferbDetailsSubform.Form.CBOSupplier Message all
>the product fields which apeeared correct on the screen go blank.

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
>
[quoted text clipped - 4 lines]
>
>|The desctiption and serial number appear when I am able to select a product

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.

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.
tina - 07 Nov 2007 04:28 GMT
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

> Marsh,
>
[quoted text clipped - 21 lines]
> 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.
>
[quoted text clipped - 180 lines]
> >>>>>>> Me.cboProduct = Null
> >>>>>>> Me.cboProduct.Requery
Marshall Barton - 07 Nov 2007 06:25 GMT
>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.

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?

Signature

Marsh
MVP [MS Access]

SG - 07 Nov 2007 23:22 GMT
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

>>FWIW, you can go to http://home.att.net/~california.db/instructions.html,
>>click on the SubformControlName link, and download a PDF file that
[quoted text clipped - 10 lines]
> did not see anything that identifies the author, is it you
> or do you know who put it together?
Marshall Barton - 08 Nov 2007 05:59 GMT
>Ahhhhhh!!!
>
[quoted text clipped - 9 lines]
>are you or someone else able to look at this I do not mind donating
>something or paying s fee if nessacery?!!

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.

Signature

Marsh
MVP [MS Access]

SG - 11 Nov 2007 22:50 GMT
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

>>Ahhhhhh!!!
>>
[quoted text clipped - 25 lines]
> form and then single stepping through each line until the
> error occurs.
Marshall Barton - 12 Nov 2007 05:22 GMT
>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
[quoted text clipped - 10 lines]
>products go blank for the previous selections - strange?? if I save this and
>go back in to the datasheet they display correctly.

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.

Signature

Marsh
MVP [MS Access]

SG - 12 Nov 2007 13:58 GMT
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!!

>>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
[quoted text clipped - 56 lines]
> So make sure you always switch to design view before making
> any change to a form/report's code module.
Marshall Barton - 12 Nov 2007 15:59 GMT
>So I have scrapped the form completly and started afresh.
>
[quoted text clipped - 12 lines]
>product in the first record reappears but the second record which has a
>different supplier name disappears and so on.

Arrgghhh, so it was a corrupted form.  Well, stuff happens
:-\

What you are seeing now is the consequence of using a
dependent combo box on a continuous or datasheet form.  The
reason is simply that there is only one RowSource property
and it is used to display all the rows.  Unfortunately, the
workaround is sort of messy.

First, modify the form's record source query to join the
products table to your form's table on the ProductID field
and add the ProductName field to the field list.

Then add a text box to the form's detail section and bind it
to the product name field.  This text box should display the
correct product name on every row in the form.

Now, to keep user actions from using the text box, add a
line of code to the text box's GotFocus event procedure:
    Me.CBOProduct.SetFocus

When you get all that working, size and position the text
box on top of the text portion of the combo box.

Signature

Marsh
MVP [MS Access]

SG - 12 Nov 2007 17:00 GMT
Marshall,

So I If I add the Product Names Field my forms list and Create the text box
with it source being the product name you are correct it does display the
correct products BUT the Runtime Error Reappears exactly the same message as
before!!

If I then remove the product Name field from the tables source query the
error goes??!!

Any Ideas?

>>So I have scrapped the form completly and started afresh.
>>
[quoted text clipped - 37 lines]
> When you get all that working, size and position the text
> box on top of the text portion of the combo box.
Marshall Barton - 12 Nov 2007 18:09 GMT
I am out of ideas on that problem.  Because what you are
trying to do is pretty common, it sure looks to me like
there is a corruption problem.  Recreating the form helped
some of it, but apparently did not completely resolve all of
it.

It's possible that creating a new, blank mdb, setting all
its database properties as needed (especially
NameAutoCorrect) and then importing everything except the
corrupt form will leave the corruption behind.  It's not
guaranteed, but worth a try.  I think decompiling after
making a backup copy of your file and before doing the
import improves the chances of a clean result.
Signature

Marsh
MVP [MS Access]

>So I If I add the Product Names Field my forms list and Create the text box
>with it source being the product name you are correct it does display the
[quoted text clipped - 46 lines]
>> When you get all that working, size and position the text
>> box on top of the text portion of the combo box.
SG - 12 Nov 2007 22:58 GMT
Marshall,

I have tried doing what you have suggested but without any success.
Would you know of anyone who would be able to take a look at this database
for me?

Once again thank you for all of your help! :)

S

>I am out of ideas on that problem.  Because what you are
> trying to do is pretty common, it sure looks to me like
[quoted text clipped - 64 lines]
>>> When you get all that working, size and position the text
>>> box on top of the text portion of the combo box.
SG - 13 Nov 2007 23:06 GMT
Marshall,

As one last ditch thought!

I changed the datasheet to a continue form. I have then created a text box
with a control source of productID which is the foriegn key from
tblProducts. If I then Select a supplier and then a product this displays ok
if I then select another supplier the text box with the control source of
productID show the correct product ID's for the previous entry. Can I then
change the primary key to be the product as apposed to having a productID
and then this will sho the product name instead of the ID???

The other issue is that the product description and serial number display
seem to alternate dependant on the supplier I choose, is there anyway we can
stop this occuring...??

Any suggestions help and advice would be gratefuly recieved before I pull
the remaining hair I have out!! :)

Kind Regards

S

>I am out of ideas on that problem.  Because what you are
> trying to do is pretty common, it sure looks to me like
[quoted text clipped - 64 lines]
>>> When you get all that working, size and position the text
>>> box on top of the text portion of the combo box.
Marshall Barton - 14 Nov 2007 05:50 GMT
>As one last ditch thought!
>
[quoted text clipped - 9 lines]
>seem to alternate dependant on the supplier I choose, is there anyway we can
>stop this occuring...??

Do NOT change any primary keys!  Your idea would work for
the immediate problem of having the combo box display
correctly on all rows, but you will run into an entirely
different set if issues if you ever need to change a product
name.

There seems to be more than a small communications gap here.
Somehow I thought you had been using a continuous form all
along.  A lot of what I told you to do was based on that and
wouldn't make sense on a datasheet form.  I guess I am
wondering what you thought I was talking about, I sure don't
understand what you were describing if you were looking at a
datasheet form???

The form's record source query was supposed to include the
product name field from the products table and the text box
should be bound to this field.  If your record source does
not join to the products table and pick up the product name
field, then only rows with the same supplier as the current
record can display the selected product name.

I feel like we're starting over from somewhere near where
this exercise began.

What does the record source query look like now?
What code do you have at this point?

Signature

Marsh
MVP [MS Access]

SG - 14 Nov 2007 08:10 GMT
Marshall,

I thought I mention the for setup anyway here is what I have...

I have a MainForm which has Tab Control On this I have a form called
frmreferb and within this I have a datasheet called
frmReferbDetailsSubformNew.

This is the record source of the frmReferbDetailsSubformNew Datasheet..

SELECT tblReferbDetails.ReferbDetailID, tblReferbDetails.ReferbID,
tblReferbDetails.Supplier, tblReferbDetails.ProductID,
tblReferbDetails.Quantity, tblReferbDetails.PricePerUnit
FROM tblReferbDetails;

I have the following controls on the datasheet...

Supplier Combo Box

Control Source = Supplier
Row Source = SELECT tblSuppliers.SupplierID, tblSuppliers.SupplierName
FROM tblSuppliers;
Bound Column 1
Column Count 2
Size 0;

After Update Event code...

Private Sub Supplier_AfterUpdate()
Me.ProductID = Null
Me.ProductID.Requery
End Sub

_______________________________________________________________________

PorductID Combo Box

Control Source = ProductID
Row Source = SELECT tblProducts.ProductID, tblProducts.ProductName,
tblProducts.ProductDescription, tblProducts.SerialNumber,
tblProducts.UnitPrice, tblProducts.SupplierID
FROM tblProducts
WHERE
(((tblProducts.SupplierID)=[Forms]![frmRoomMain]![frmReferb].[Form]![frmReferbDetailsSubformNew].[Form]![Supplier]));
Bound Column = 1
Coulmn Count = 6
Column Widths = 3cm;5cm;0cm;0cm;0cm;0cm;0cm

No Code
________________________________________________________________
Text Box Called CBOProductDesription
Control Source =ProductID.Column(2)

__________________________________________________________

Text Box called SerialNumber
Control Source =ProductID.Column(1)

__________________________________________________________

Text Box Called Quantity
Control Source= Quantity

_________________________________________________

Text Box Called PricePerUnit
Control Source = PricePerUnit

This is all I have on the datasheet..

Kind Regards & Fingers Crossed ;)

>>As one last ditch thought!
>>
[quoted text clipped - 38 lines]
> What does the record source query look like now?
> What code do you have at this point?
Marshall Barton - 15 Nov 2007 15:59 GMT
>I have a MainForm which has Tab Control On this I have a form called
>frmreferb and within this I have a datasheet called
[quoted text clipped - 57 lines]
>
>This is all I have on the datasheet..

And the problems are:
1) the product combo box does not display the product name
2) the product description and serial number text boxes
    display the same thing on every row
3) when you navigate to another record the product drop list
    still shows the products from the last selected suppier
4) If you fix 1), then the product combo box has the same
    problem as 2)
Right?

You can fix 1) by changing the ProductID combo box's
Column Widths to  0cm;5cm;0cm;0cm;0cm;0cm;0cm

You can fix 3) by adding a line of code to the subform's
Current event:
    Me.ProductID.Requery

I already described how to fix 4) but it has no hope of
working on a datasheet form.  At one point you said you
tried using a continuous form, but I lost track of what did
and did not work in that experiment.  You will have to live
with both 2) and 4) unless you use a continuous form.

If you do change it to a continuous form, then you can solve
2) by adding the description and serial fields to the form's
record source along with the product name field and binding
the text boxes to those fields.  Both of these text boxes
should be locked and disabled.

Signature

Marsh
MVP [MS Access]

SG - 17 Nov 2007 14:58 GMT
Marshall,

Just a quick word to say thankyou for all of your help! Finally I managed to
get this sorted out.

Kind Regards

S
>>I have a MainForm which has Tab Control On this I have a form called
>>frmreferb and within this I have a datasheet called
[quoted text clipped - 86 lines]
> the text boxes to those fields.  Both of these text boxes
> should be locked and disabled.
tina - 08 Nov 2007 06:20 GMT
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!

> >FWIW, you can go to http://home.att.net/~california.db/instructions.html,
> >click on the SubformControlName link, and download a PDF file that
[quoted text clipped - 9 lines]
> did not see anything that identifies the author, is it you
> or do you know who put it together?
tina - 04 Nov 2007 16:34 GMT
see http://www.mvps.org/access/forms/frm0028.htm.

hth

> I have a frmreferb form and frmrefurbsub form.
>
[quoted text clipped - 10 lines]
>
> S
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.