I have a database with 30 records and growing. When I scroll through the
records, two combo boxes become blank even though the data is contained in
the table.

The first combo box is the "company" and the second combo box is the "Sales
Rep". When you select the company combo box it show the appropriate sales
reps that are part of that client.

Any suggestions on why the fields become blank?

In article <B79B0563-4E53-4EC0-9265-(E-Mail Removed)>,
(E-Mail Removed) says...
> I have a database with 30 records and growing. When I scroll through the
> records, two combo boxes become blank even though the data is contained in
> the table.
>
> The first combo box is the "company" and the second combo box is the "Sales
> Rep". When you select the company combo box it show the appropriate sales
> reps that are part of that client.
>
> Any suggestions on why the fields become blank?
>
>
>

Are you displaying a name field of the lookup table in the combobox,
while hiding the key value? If the rowsource of a combobox doesn't
include the current key value, the combobox will appear blank, even
though there really is a value in the field.

As you scroll through your records, your rowsource may not include the
lookup values in the rows anymore. Try updating the SQL statement in
the rowsource in the On Current event to keep it in sync.

--
Armen Stein
Microsoft Access MVP
www.JStreetTech.com

This is the SQL statement in the rowsource. I'm not sure where the "On
Current" event is located. I see the on click, on enter, on exit, etc.

SELECT Representative.RepresentativeID, [Representative]![First Name] & " "
& [Representative]![Last Name] AS Expr1
FROM Representative
WHERE (((Representative.ClientID)=[Forms]![Claimant]![ClientID]));

Thanks in advance for the help. Any suggestions is greatly appreciated.

"Armen Stein" wrote:

> In article <B79B0563-4E53-4EC0-9265-(E-Mail Removed)>,
> (E-Mail Removed) says...
> > I have a database with 30 records and growing. When I scroll through the
> > records, two combo boxes become blank even though the data is contained in
> > the table.
> >
> > The first combo box is the "company" and the second combo box is the "Sales
> > Rep". When you select the company combo box it show the appropriate sales
> > reps that are part of that client.
> >
> > Any suggestions on why the fields become blank?
> >
> >
> >
>
> Are you displaying a name field of the lookup table in the combobox,
> while hiding the key value? If the rowsource of a combobox doesn't
> include the current key value, the combobox will appear blank, even
> though there really is a value in the field.
>
> As you scroll through your records, your rowsource may not include the
> lookup values in the rows anymore. Try updating the SQL statement in
> the rowsource in the On Current event to keep it in sync.
>
> --
> Armen Stein
> Microsoft Access MVP
> www.JStreetTech.com
>
>

Here is the current code in the On Current event:

Private Sub Form_Current()
If IsNull(Me![AutoCaseNumber]) Then
DoCmd.GoToControl "AutoCaseNumber"
End If
End Sub

I'm still new to Access so any additional guidance is helpful.



"Armen Stein" wrote:

> In article <B79B0563-4E53-4EC0-9265-(E-Mail Removed)>,
> (E-Mail Removed) says...
> > I have a database with 30 records and growing. When I scroll through the
> > records, two combo boxes become blank even though the data is contained in
> > the table.
> >
> > The first combo box is the "company" and the second combo box is the "Sales
> > Rep". When you select the company combo box it show the appropriate sales
> > reps that are part of that client.
> >
> > Any suggestions on why the fields become blank?
> >
> >
> >
>
> Are you displaying a name field of the lookup table in the combobox,
> while hiding the key value? If the rowsource of a combobox doesn't
> include the current key value, the combobox will appear blank, even
> though there really is a value in the field.
>
> As you scroll through your records, your rowsource may not include the
> lookup values in the rows anymore. Try updating the SQL statement in
> the rowsource in the On Current event to keep it in sync.
>
> --
> Armen Stein
> Microsoft Access MVP
> www.JStreetTech.com
>
>

In article <1D17896B-4954-4086-BE8A-(E-Mail Removed)>,
(E-Mail Removed) says...
> This is the SQL statement in the rowsource. I'm not sure where the "On
> Current" event is located. I see the on click, on enter, on exit, etc.
>
> SELECT Representative.RepresentativeID, [Representative]![First Name] & " "
> & [Representative]![Last Name] AS Expr1
> FROM Representative
> WHERE (((Representative.ClientID)=[Forms]![Claimant]![ClientID]));
>

Your Where clause is being set to the value on your form, so the method
I suggested of setting it in the On Current event isn't necessary.

What is the Company Rowsource?

To try to figure out what's happening, bring up a record where you have
the weird blanks. Open a new query in SQL view, and paste the Rowsource
SQL into it. Run it and see what you get. Try it for both Company and
Representatve. For the combobox to show blank, the value that you
already have on the form is not in the records returned. This will give
you a clue as to what's going on.

--
Armen Stein
Microsoft Access MVP
www.JStreetTech.com

The representative field seems to be having the issue:

SELECT Representative.RepresentativeID, [Representative]![First Name] & " "
& [Representative]![Last Name] AS Expr1
FROM Representative
WHERE (((Representative.ClientID)=[Forms]![Claimant]![ClientID]));

As you would suspect, when run as a query, it requests the ClientID
information

It is looking for the ClientID from the current form which is loaded. It
appears that the ClientID is holding the original first value and works fine
for any records that are using the same ClientID field. The ClientID field is
showing the correct value though it does not seem to be updating the stored
information for ClientID.

SELECT Client.ClientID, Client.[Client Name]
FROM Client
ORDER BY Client.[Client Name];

The "tables" contains the correct data so once once the ClientID is
reselected, the information shows.

Any insight or suggestions are appreacited.

I added a Macro that has a requery for both fields and it seems to have
corrected the problem.

Thanks again for all the help and if you have any other ideas or
suggestions, they are always appreciated.

"Zanstemic" wrote:

> The representative field seems to be having the issue:
>
> SELECT Representative.RepresentativeID, [Representative]![First Name] & " "
> & [Representative]![Last Name] AS Expr1
> FROM Representative
> WHERE (((Representative.ClientID)=[Forms]![Claimant]![ClientID]));
>
> As you would suspect, when run as a query, it requests the ClientID
> information
>
> It is looking for the ClientID from the current form which is loaded. It
> appears that the ClientID is holding the original first value and works fine
> for any records that are using the same ClientID field. The ClientID field is
> showing the correct value though it does not seem to be updating the stored
> information for ClientID.
>
> SELECT Client.ClientID, Client.[Client Name]
> FROM Client
> ORDER BY Client.[Client Name];
>
> The "tables" contains the correct data so once once the ClientID is
> reselected, the information shows.
>
> Any insight or suggestions are appreacited.
>
>