Form, Como Box, Subform problem

[Cindy K]

I have a form with a combo box to select the record, or CustNo I want to
view. The form also has a sub form. Both are linked by CustNo. My problem is
when I select a value, CustNo, from the combo box, the record found is not
the correct CustNo OR the record in the subform is not the correct CustNo
record. It does not happen all the time and not consistently. I have several
users of the application deployed over the network but only one uses this
form. What would cause this?

 

[Dirk Goldgar]

I have a form with a combo box to select the record, or CustNo I want
to view. The form also has a sub form. Both are linked by CustNo. My
problem is when I select a value, CustNo, from the combo box, the
record found is not the correct CustNo OR the record in the subform
is not the correct CustNo record. It does not happen all the time and
not consistently. I have several users of the application deployed
over the network but only one uses this form. What would cause this?

Questions:

1. Could there be more than one record on the main form with the same
CustNo?

2. Is CustNo the primary key of the main form's recordsource?

3. Is the combo box bound or unbound? (should be unbound)

4. What is the rowsource of the combo box? If a query, what is its SQL?

5. What is the code behind the combo box, that finds the record?

6. What version and service-pack level of Access are you using?

7. If you know what version and SP level of Jet you are using, what is
it?

 

[Cindy K]

Questions:

1. Could there be more than one record on the main form with the same
CustNo? A. No
2. Is CustNo the primary key of the main form's recordsource? A.No, but is unique
3. Is the combo box bound or unbound? (should be unbound) A.Unbound
4. What is the rowsource of the combo box? If a query, what is its SQL?

A. Select CustNo,Name,, from Customer (nothing fancy here)

5. What is the code behind the combo box, that finds the record?

A.Private Sub CustCombo_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustNo] = " & Str(Nz(Me![CustCombo], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

6. What version and service-pack level of Access are you using?

A. Access 2003(11.6355.6360) SP1

7. If you know what version and SP level of Jet you are using, what is
it?

A. Jet version is 4.0 I don't see an SP level on that

 

[Dirk Goldgar]

Questions:

1. Could there be more than one record on the main form with the same
CustNo? A. No
2. Is CustNo the primary key of the main form's recordsource? A.No, but is unique
3. Is the combo box bound or unbound? (should be unbound) A.Unbound
4. What is the rowsource of the combo box? If a query, what is its
SQL?

A. Select CustNo,Name,, from Customer (nothing fancy here)

5. What is the code behind the combo box, that finds the record?

A.Private Sub CustCombo_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustNo] = " & Str(Nz(Me![CustCombo], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

6. What version and service-pack level of Access are you using?

A. Access 2003(11.6355.6360) SP1

7. If you know what version and SP level of Jet you are using, what
is it?

A. Jet version is 4.0 I don't see an SP level on that

Hmm, it all looks kosher to me. What data type is CustNo? What's the
bound column of the combo box?

 

[Cindy K]

Hmm, it all looks kosher to me. What data type is CustNo? What's the
bound column of the combo box?

The data type for CustNo is Number. The first column is the bound column on
the combo box. Can it have something to do with the pointers? I am assuming
they are all unchangeable within Access. The problem does not occur on a
regular basis but this form is used alot during the day and has pointed to
the wrong records many times, enough to hinder production. I can't even get
the problem to occur consistently on a particular record or data entry style.
It just happens as if it forgets what record we are looking for and throws
just anything up. Any ideas?

 

[Dirk Goldgar]

The data type for CustNo is Number. The first column is the bound
column on the combo box. Can it have something to do with the
pointers? I am assuming they are all unchangeable within Access. The
problem does not occur on a regular basis but this form is used alot
during the day and has pointed to the wrong records many times,
enough to hinder production. I can't even get the problem to occur
consistently on a particular record or data entry style. It just
happens as if it forgets what record we are looking for and throws
just anything up. Any ideas?

I'm rather perplexed at the moment. It may be that the form is
corrupted in some way. Is this a split database, with each user having
her own copy of the front-end, or are multiple users opening the same
database file?

If the form is corrupted, you may have to recreate it from scratch.
Before doing that, though, you might try decompiling the database. You
would do this on a copy of the database, not on the original, and then
if all went well you would swap in the copy you worked on.

Here's the general procedure for decompiling:

1. Make sure you have a backup copy!

2. With no users in the database, execute a command line similar to this
via Start -> Run:

msaccess.exe /decompile "C:\Path To\YourDB.mdb"

You may have to specify the full path to msaccess.exe as well.

3. Open the database and compact it.

4. Compile the database (in VB Editor, click Debug -> Compile).

5. Compact the database again.

See if that fixes the problem.

If that *doesn't* fix the problem, you might try to get rid of
corruption by saving the form as text, deleting it, and reloading it,
using the undocumented Application.SaveAsText and .LoadFromText methods.
Here's how:

1. Click Tools -> Options, go to the General tab, and turn off Name
AutoCorrect if it's on.

2. Press Ctrl+G to open the Immediate window.

3. Enter the following command in the Immediate window, substituting
your form's name:

Application.SaveAsText acForm, "YourForm",
"C:\Temp\YourForm.txt"

4. Delete or rename the original form in the database.

5. Enter the following command in the Immediate window, substituting
your form's name:

Application.LoadFromText acForm, "YourForm",
"C:\Temp\YourForm.txt"

5. Recompile your database.

If that doesn't fix the problem, then I think your next step is to
rebuild the form from scratch.

 

[Cindy K]

I have recreated this form from scratch, decompiled and recompiled. Nothing
seems to help. The database uses linked tables and is deployed over the
network. Is there something to the old Bookmark bug that might still be
unfixed in the later versions of access?

 

[Dirk Goldgar]

I have recreated this form from scratch, decompiled and recompiled.
Nothing seems to help. The database uses linked tables and is
deployed over the network. Is there something to the old Bookmark bug
that might still be unfixed in the later versions of access?

As far as I know, the bookmark bug was vanquished long ago. I suppose
this could be a new bug, but I haven't heard any similar complaints. I
suppose it could also be a network bug of some kind, but I just don't
know enough about such matters to know if that's plausible. Is there
any other code behind the form or subform, or even in an active Timer
event on some other open form?

 

[Cindy K]

This is a very simplistic form/subform with combo box. I have stripped all
event triggers I could think of, ran across the network and locally. Still
have the problem. It is not consistent with records, time or events so it is
very elusive to trap. I am going to chalk this up to a new bug and revamp the
processing of my form to hold only 1 record so I can avoid bookmarks. If you
hear of anthing else similar, please respond. Thank you for your effort.

 

[SirPoonga]

Have you tried to see if you get the same results by creating the query
outside of the form?

I never knew about the Nz function until now....

 

[Dirk Goldgar]

This is a very simplistic form/subform with combo box. I have
stripped all event triggers I could think of, ran across the network
and locally. Still have the problem. It is not consistent with
records, time or events so it is very elusive to trap. I am going to
chalk this up to a new bug and revamp the processing of my form to
hold only 1 record so I can avoid bookmarks. If you hear of anthing
else similar, please respond. Thank you for your effort.

I haven't given up yet! I'm going to ask some of my colleagues to have
a look at this problem. Also, there's a question I didn't ask you. Do
both the form and subform have a control on the form for the CustNo
field?

 

[Cindy K]

I can make the form work if I get rid of the bookmarks and force the form to
look at only 1 record based on a query triggered by a change in the combo
box. That works great but I can't scroll through more than 1 record that way.
It makes it pretty cumbersome when you really need to have that
functionality. I have narrowed the problem down to using the bookmark feature.

 

[Cindy K]

The CustNo field is selected from the list in the ComboBox. When it changes
or is selected, the form record should point to that chosen CustNo. This is
also the same field that ties the form to the subform. Does that answer your
question?

 

[Dirk Goldgar]

The CustNo field is selected from the list in the ComboBox. When it
changes or is selected, the form record should point to that chosen
CustNo. This is also the same field that ties the form to the
subform. Does that answer your question?

No, not really, because the combo box isn't bound to that field. Is
there a text box or other control that is actually bound to the CustNo
field? And similarly for the subform, is there a control on the subform
that is actually bound to the matching CustNo field in the related
table?

 

[Tony Toews]

I have a form with a combo box to select the record, or CustNo I want to
view. The form also has a sub form. Both are linked by CustNo. My problem is
when I select a value, CustNo, from the combo box, the record found is not
the correct CustNo OR the record in the subform is not the correct CustNo
record. It does not happen all the time and not consistently. I have several
users of the application deployed over the network but only one uses this
form. What would cause this?

Do the number of columns as specified in the combo boxes Column Count
property match the number of columns in the query?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Cindy K]

Yes the number of columns match that in the property box

 

[Cindy K]

There is a field on the form bound to CustNo. I have set all fields on the
form to display only. None can be added, edited, deleted. The subform also
has a field bound to CustNo which is display only. There are only 3 fields
where I am allowing data to be entered. None of which are defined as a key or
index within the table. This subform allows additions, edits and deletes.

 

[Dirk Goldgar]

There is a field on the form bound to CustNo. I have set all fields
on the form to display only. None can be added, edited, deleted. The
subform also has a field bound to CustNo which is display only. There
are only 3 fields where I am allowing data to be entered. None of
which are defined as a key or index within the table. This subform
allows additions, edits and deletes.

Hmm, at this point I'm at a loss. As I said, I haven't heard anything
about any recurrence of the old "bookmark bug", or any symptoms similar
to what you're reporting. Have you visited the Office Update and
Windows Update sites to make sure your system is completely up to date?
(I have to say, it sounds from your previous messages as though you are,
but it doesn't hurt to make sure.)

Would you care to send me a cut-down copy of your database, containing
just the relevant objects and enough data to demonstrate the problem?
If you want to, I'll give you an address to send it to.

 

[Ken Snell [MVP]]

Also, the current Jet update can be obtained this way:

How to obtain the latest service pack for the Microsoft Jet 4.0 Database
Engine
http://support.microsoft.com/default.aspx?scid=kb;en-us;239114

 

[Cindy K]

I am up to date with all updates from Microsoft. That was actually my first
move. I wish I could send out a copy, but my hands are tied with that. I have
noticed that as the number of records grow, the problem occurs more often.
I'm not sure if it is the same bug but the symptoms seem to fit. I appreciate
all your help and information.