recordsource error

[ColinWard]

Hi. I have a contacts form with an unbound dropdown box and a bunch of
bound controls relating to the contact. When I load the form, I want all
the controls to be empty until I select a name from the dropdown. this
works except that as expected, all the bound controls have #Name? in
them when the form loads until I select a name. the controls are then
bound and the record displays properly.

How I can do this without getting the #Name? error?

thank you

Colin
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Pavel Romashkin]

Make the entire form unbound, then bind it only after the selection is
made in the combo box. You can leave the binding for individual controls
intact - it will work when you provide the proper record source.

Pavel

ColinWard wrote:

Hi. I have a contacts form with an unbound dropdown box and a bunch of
bound controls relating to the contact. When I load the form, I want all
the controls to be empty until I select a name from the dropdown. this
works except that as expected, all the bound controls have #Name? in
them when the form loads until I select a name. the controls are then
bound and the record displays properly.

How I can do this without getting the #Name? error?

thank you

Colin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[ColinWard]

Hi Pavel. thanks for your reply. I tried doing as you suggested by
removing the form's recordsource but I still got the error. It still
worked after I seleccted a name from the dropdown. do I have to set each
individual control's recordsource to null or was the way I did it the
correct way?

Thanks for your help

Colin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Pavel Romashkin]

Colin,
I am sorry, I sent you down the wrong path :-(
Try setting the recordsource to return zero records when the form first
opens, like

SELECT * FROM SourceTable WHERE False

Don't forget to change it back later :-)
Pavel

ColinWard wrote:

Hi Pavel. thanks for your reply. I tried doing as you suggested by
removing the form's recordsource but I still got the error. It still
worked after I seleccted a name from the dropdown. do I have to set each
individual control's recordsource to null or was the way I did it the
correct way?

Thanks for your help

Colin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[ColinWard]

Thanks Pavel!!!

It works perfectly.

Now I have another question. this one is quite complicated but I will
see if I can explain.

I have two textboxes on my contacts form which are unbound. The first of
these is a list of products which the current contact is interested in
and the second listbox is a description of the product that is currently
selected in the first listbox. When I press the insert key on the
products listbox, another form pops up which allows me to select all the
products which the current contact is interested in. I then click a
button on the form and the selected products are transferred to the
first listbox on the contacts form via an SQL statement. then if I
select any of these newly added products, the proper description is
displayed. The problem comes when I try to delete any of the products
from the contact. The problem is that the second listbox gets its data
from a different table than the first listbox and I use the column
property to populate the second listbox. when I delete a product, the
product description is not deleted but I do not want it to be because
the description comes from the products table itself, whereas the
products are added to an Add Products to Contacts table. I hope this
makes sense.

here is the query that populates the first listbox.
---
SELECT [Products by Contact Query].ContactID, [Products by Contact
Query].ProductID, [Products by Contact Query].ProductName,
Products.ProductDescription FROM [Products by Contact Query] INNER JOIN
Products ON [Products by Contact Query].ProductID=Products.ProductID;
----
and here is the code that provides the description of the selected
product.

Private Sub LstContactProducts_AfterUpdate()
txtProductDescription.SetFocus
txtProductDescription.Value = LstContactProducts.Column(3,
LstContactProducts.ItemsSelected)
LstContactProducts.SetFocus
End Sub

My question is simply this:

how do I delete the product description from the product given that the
list boxes are populated from two different source tables? I cannot
delete the product description directly as it is part of the Products
table and not part of the Add Products to Contacts table.
I hope this makes sense!!!

Thanks for all your all help Pavel

Colin



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Pavel Romashkin]

Colin,

I don't see a problem pulling a description of the product from the
column of the listbox to put it into a textbox. It does not matter that
the data comes from another table because for the controls to work the
data is just the result of a valid SQL - the lis box doesn't care what
the cources of this SQL are.
I think the problem you are having can be remedied if:
1) you refresh the listbox after the item is deleted from the customer
items list (I suppose, in that pop-up form where you control the items
for the customers)
2) you force the update of txtProductDescription.Value not just in
LstContactProducts_AfterUpdate but also in the code where you are
removing items from the customers choices list.

Cheers,
Pavel

ColinWard wrote:

Thanks Pavel!!!

It works perfectly.

Now I have another question. this one is quite complicated but I will
see if I can explain.

I have two textboxes on my contacts form which are unbound. The first of
these is a list of products which the current contact is interested in
and the second listbox is a description of the product that is currently
selected in the first listbox. When I press the insert key on the
products listbox, another form pops up which allows me to select all the
products which the current contact is interested in. I then click a
button on the form and the selected products are transferred to the
first listbox on the contacts form via an SQL statement. then if I
select any of these newly added products, the proper description is
displayed. The problem comes when I try to delete any of the products
from the contact. The problem is that the second listbox gets its data
from a different table than the first listbox and I use the column
property to populate the second listbox. when I delete a product, the
product description is not deleted but I do not want it to be because
the description comes from the products table itself, whereas the
products are added to an Add Products to Contacts table. I hope this
makes sense.

here is the query that populates the first listbox.
---
SELECT [Products by Contact Query].ContactID, [Products by Contact
Query].ProductID, [Products by Contact Query].ProductName,
Products.ProductDescription FROM [Products by Contact Query] INNER JOIN
Products ON [Products by Contact Query].ProductID=Products.ProductID;
----
and here is the code that provides the description of the selected
product.

Private Sub LstContactProducts_AfterUpdate()
txtProductDescription.SetFocus
txtProductDescription.Value = LstContactProducts.Column(3,
LstContactProducts.ItemsSelected)
LstContactProducts.SetFocus
End Sub

My question is simply this:

how do I delete the product description from the product given that the
list boxes are populated from two different source tables? I cannot
delete the product description directly as it is part of the Products
table and not part of the Add Products to Contacts table.

I hope this makes sense!!!

Thanks for all your all help Pavel

Colin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!