list box updating

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please help me with the code for a command button to do the following:

My form has a list box of Doctors. I need to pick one, then click a command
button that will open a form, to edit his data. When that form is closed the
list box is updated.

I don't have much code worth showing but here is what works.

Private Sub cmdAddProv_Click()
Dim stDocName As String

????

stDocName = "f_ProviderDetail"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub
 
Dan

Why a listbox? They take up a lot of room on the screen.

How about using a combobox, typing the start of the name, then selecting the
right one.

You could use this approach and stay on the same form (no need to open
another form). Generically, this would work by having the combobox you use
to select the Doctor be unbound, using the combobox's AfterUpdate event to
requery the form, base the form on a query, and in the query, use the value
in the combobox as a criterion for the recordID to show in the form.

When you first open this form, no one's selected in the combobox, so no
one's data is displayed. After picking someone, the query runs using the
Doctor selected, filling the form with that Doctor's data.

?And what do you mean by "the listbox is updated"? ?Updated how?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Very Close!!
I created the combo box. When one is picked, the form to enter data comes
up, for that Doctor. But I'm have bound/unbound problems!!

The combo box shows every Doctor on every main form record...
 
Take another look at the query that fills the form. Use the Doctor selected
in the combobox as a selection criterion to limit the records displayed in
the form.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I've been trying to do what your suggesting....
The combobox is called "cmbProv"
When I add it to the criteria in the query it no longer allows the doctors
to show up in the combo box....


SELECT tblQualityProvider.ProvNo, tblQualityProvider.ICNNo,
tblQualityProvider.Complaint, tblQualityProvider.ProvName
FROM tblQualityProvider
WHERE (((tblQualityProvider.ProvNo)="cmbProv"));
 
Dan

The combobox is located on a form, right? Unless you tell Access where to
look, it doesn't know. The way you told Access to look, Access thinks it's
to find the literal string "cmbProv" in the [ProvNo] field. Not gonna
happen, is it?!?

And unless the form with the combobox is actually opened, telling Access to
look on that form, in that combobox won't work, because nothing will be
there.

Try:
WHERE (((tblQualityProvider.ProvNo)= Forms!YourFormName!cmbProv));
Click to expand...

This tells Access to look on the form (remember to use the actual name of
YOUR form) in the control named "cmbProv" for a value to test against. Now,
what data type is [ProvNo]? The name of the field implies that it is
numeric. If it is a "text" type field, you'll also need to add quotes.

Instead of using SQL, you could do this in the query design view and let
Access worry about the SQL statement...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
With the Query below this is what happens!
I pick a Provider from the combo box and obviously that one provider is then
listed.
If I then run the query only that one Providers information is displayed.
So, I know it picks up the one provider requested.
But I need to be able to display multiple providers linked to that record
and have the ability to add Providers.


SELECT tblQualityProvider.ProvNo, tblQualityProvider.ICNNo,
tblQualityProvider.Complaint, tblQualityProvider.ProvName
FROM tblQualityProvider
WHERE (((tblQualityProvider.ProvNo)=[Forms]![frmQualityData]![cmbProv]));

Suggestions? I've tried adding a list box but it says no value assigned?




Jeff Boyce said:
Dan

The combobox is located on a form, right? Unless you tell Access where to
look, it doesn't know. The way you told Access to look, Access thinks it's
to find the literal string "cmbProv" in the [ProvNo] field. Not gonna
happen, is it?!?

And unless the form with the combobox is actually opened, telling Access to
look on that form, in that combobox won't work, because nothing will be
there.

Try:
WHERE (((tblQualityProvider.ProvNo)= Forms!YourFormName!cmbProv));
Click to expand...

This tells Access to look on the form (remember to use the actual name of
YOUR form) in the control named "cmbProv" for a value to test against. Now,
what data type is [ProvNo]? The name of the field implies that it is
numeric. If it is a "text" type field, you'll also need to add quotes.

Instead of using SQL, you could do this in the query design view and let
Access worry about the SQL statement...

Regards

Jeff Boyce
Microsoft Office/Access MVP



Dan @BCBS said:
I've been trying to do what your suggesting....
The combobox is called "cmbProv"
When I add it to the criteria in the query it no longer allows the doctors
to show up in the combo box....


SELECT tblQualityProvider.ProvNo, tblQualityProvider.ICNNo,
tblQualityProvider.Complaint, tblQualityProvider.ProvName
FROM tblQualityProvider
WHERE (((tblQualityProvider.ProvNo)="cmbProv"));
Click to expand...
Click to expand...
 
Dan

Everything starts with the data and relationships in Access.

I don't have a sense of what data structure you are using, so it's tough to
offer specific suggestions.

When you "pick a Provider from the combo box", do you mean you are doing
that in the Query? If so, does your table include a "lookup" field for the
Provider? Lookup fields in tables cause considerable confusion and require
close attention to the fact that what is stored and and is displayed are two
different things.

More information about the data structure, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Dan @BCBS said:
With the Query below this is what happens!
I pick a Provider from the combo box and obviously that one provider is
then
listed.
If I then run the query only that one Providers information is displayed.
So, I know it picks up the one provider requested.
But I need to be able to display multiple providers linked to that record
and have the ability to add Providers.


SELECT tblQualityProvider.ProvNo, tblQualityProvider.ICNNo,
tblQualityProvider.Complaint, tblQualityProvider.ProvName
FROM tblQualityProvider
WHERE (((tblQualityProvider.ProvNo)=[Forms]![frmQualityData]![cmbProv]));

Suggestions? I've tried adding a list box but it says no value assigned?




Jeff Boyce said:
Dan

The combobox is located on a form, right? Unless you tell Access where
to
look, it doesn't know. The way you told Access to look, Access thinks
it's
to find the literal string "cmbProv" in the [ProvNo] field. Not gonna
happen, is it?!?

And unless the form with the combobox is actually opened, telling Access
to
look on that form, in that combobox won't work, because nothing will be
there.

Try:
WHERE (((tblQualityProvider.ProvNo)= Forms!YourFormName!cmbProv));
Click to expand...

This tells Access to look on the form (remember to use the actual name of
YOUR form) in the control named "cmbProv" for a value to test against.
Now,
what data type is [ProvNo]? The name of the field implies that it is
numeric. If it is a "text" type field, you'll also need to add quotes.

Instead of using SQL, you could do this in the query design view and let
Access worry about the SQL statement...

Regards

Jeff Boyce
Microsoft Office/Access MVP



Dan @BCBS said:
I've been trying to do what your suggesting....
The combobox is called "cmbProv"
When I add it to the criteria in the query it no longer allows the
doctors
to show up in the combo box....


SELECT tblQualityProvider.ProvNo, tblQualityProvider.ICNNo,
tblQualityProvider.Complaint, tblQualityProvider.ProvName
FROM tblQualityProvider
WHERE (((tblQualityProvider.ProvNo)="cmbProv"));
Click to expand...
Click to expand...
Click to expand...
 
I start over with a more informative string.
The subject will be: List vs Combo

Jeff Boyce said:
Dan

Everything starts with the data and relationships in Access.

I don't have a sense of what data structure you are using, so it's tough to
offer specific suggestions.

When you "pick a Provider from the combo box", do you mean you are doing
that in the Query? If so, does your table include a "lookup" field for the
Provider? Lookup fields in tables cause considerable confusion and require
close attention to the fact that what is stored and and is displayed are two
different things.

More information about the data structure, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Dan @BCBS said:
With the Query below this is what happens!
I pick a Provider from the combo box and obviously that one provider is
then
listed.
If I then run the query only that one Providers information is displayed.
So, I know it picks up the one provider requested.
But I need to be able to display multiple providers linked to that record
and have the ability to add Providers.


SELECT tblQualityProvider.ProvNo, tblQualityProvider.ICNNo,
tblQualityProvider.Complaint, tblQualityProvider.ProvName
FROM tblQualityProvider
WHERE (((tblQualityProvider.ProvNo)=[Forms]![frmQualityData]![cmbProv]));

Suggestions? I've tried adding a list box but it says no value assigned?




Jeff Boyce said:
Dan

The combobox is located on a form, right? Unless you tell Access where
to
look, it doesn't know. The way you told Access to look, Access thinks
it's
to find the literal string "cmbProv" in the [ProvNo] field. Not gonna
happen, is it?!?

And unless the form with the combobox is actually opened, telling Access
to
look on that form, in that combobox won't work, because nothing will be
there.

Try:

WHERE (((tblQualityProvider.ProvNo)= Forms!YourFormName!cmbProv));

This tells Access to look on the form (remember to use the actual name of
YOUR form) in the control named "cmbProv" for a value to test against.
Now,
what data type is [ProvNo]? The name of the field implies that it is
numeric. If it is a "text" type field, you'll also need to add quotes.

Instead of using SQL, you could do this in the query design view and let
Access worry about the SQL statement...

Regards

Jeff Boyce
Microsoft Office/Access MVP



I've been trying to do what your suggesting....
The combobox is called "cmbProv"
When I add it to the criteria in the query it no longer allows the
doctors
to show up in the combo box....


SELECT tblQualityProvider.ProvNo, tblQualityProvider.ICNNo,
tblQualityProvider.Complaint, tblQualityProvider.ProvName
FROM tblQualityProvider
WHERE (((tblQualityProvider.ProvNo)="cmbProv"));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top