not in list problem

[Guest]

I'm having a problem with a routine that i'm usnig to add an item that
doesn't exist in a table that is the source for a combo box. the problem is
that if i run the code in step mode it works perfect, the code calls another
form in modal mode, i add the item to the table, i minimize the form return
to original form code, i close the other form i set the response varaible to
reflect that the item has been added and the new item sows up on the combo
box. but if run it in normal mode everything works except that access for
whatever reason dosen't requery the combo box and it's giving me the error
message that the item dosen't exit and if want to add it, i check the table
and the item it's there but the combobox dosen't reflect the addition.

could you help me

 

[Ken Snell [MVP]]

Post the codes that you're using...

Hard to troubleshoot something sight unseen.

 

[Guest]

what do you need?

Post the codes that you're using...

Hard to troubleshoot something sight unseen.

 

[Ken Snell [MVP]]

Start with posting the code steps that run in your modules.

 

[Guest]

It has 2 command buttons, one to save the record and other to close the form,
the on click event of the save command button it has code that checks if the
form was open in modal form and if that's true then it turns the form
invisible.
so that's not the problem. as i describe in the original post if run the
code in step mode it work fine but if i run it in normal mode it dosen't.

 

[Ken Snell [MVP]]

Only other thing that comes to mind is a code timing issue. Put a
DoEvents

step after the
DoCmd.Close acForm, "frmLocations"

step. It may be that the data aren't saving fast enough to be seen when the
combo box is requeried. This is consistent with it "working" when you step
through the code, as that takes a lot longer to get through the steps,
giving the data save more time to occur.
--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

well that didn't work, but that gave and idea, i tried and it worked. i put a
for next loop for 10000000 instead of the doevents(). Now my question is why?

 

[Ken Snell [MVP]]

Hard to say without knowing more about your connection setup and operating
system and PC configuaration and how many other users you may have. Timing
issues are not uncommon.

One other thing you could do would be to explicitly save the record in the
popup form when you click the Save button. Right now, your code just makes
the form invisible; if you add before that step this step:
If Me.Dirty = =True Then Me.Dirty = False

this will cause the form to save the record before it makes the form
invisible, and then you shouldn't need the For loop that you added.
--

Ken Snell
<MS ACCESS MVP>

 

[Dirk Goldgar]

It has 2 command buttons, one to save the record and other to close
the form, the on click event of the save command button it has code
that checks if the form was open in modal form and if that's true
then it turns the form invisible.
so that's not the problem. as i describe in the original post if run
the
code in step mode it work fine but if i run it in normal mode it
dosen't.

How do you perform this check for "modal"? What's the code? It sounds
as if maybe it's not correctly identifying the fact that the form was
opened in dialog mode. If you're checking the form's Modal property, I
find that it does *not* indicate whether the form was opened with the
acDialog argument.

 

[Guest]

well guess what, sometimes it will work and some other times will not, i even
change the count all the way up to 90000000 and still it will only work 4 out
of 5 times. i'm using a pentium iii 450 mhz laptop with 192mb of ram with
win2k professional with all the updates, using office xp with all the
updates. I'm testing the application locally but i.m planning to put it on a
server so 3 to 5 users can use it, not all of the the same time. i've split
the files from the forms & code to another database. On your question about
the popup form i'm explicity saving the record, as i explain before i can go
to the table and the record is there, the problem is with the combo box not
requerin the data properly.

 

[Guest]

the way that i'm checking that the form was opened in modal modal is by
checking the length of the openargs variable, and that part is working fine
because if i open the form normally it work normally(it dosen't just hide
after clicking on the save button), also i have code on the save button to
explicity save the record and that also works, i had explained before the
issue is with the requering of the data on the combo box

 

[Dirk Goldgar]

the way that i'm checking that the form was opened in modal modal is
by checking the length of the openargs variable, and that part is
working fine because if i open the form normally it work normally(it
dosen't just hide after clicking on the save button), also i have
code on the save button to explicity save the record and that also
works, i had explained before the issue is with the requering of the
data on the combo box

I've never seen the problem you describe.

Have we established whether you are working in an Access MDB file or an
ADP? If it's an MDB, is the table to which a record is being added a
local Jet table (i.e., in the current MDB file), a foreign Jet table
accessed as a linked one in the current database, a linked ODBC table,
or (maybe) a foreign table that is not linked in this database?

Is frmLocations bound to this table, or is it unbound, updating the
table via an update/append query or a separate recordset? Please post
the code behind the Save button on that form.

 

[Guest]

hi there, i'm working with MDB files, but the tables are linked. Both forms
are unbound and it will hard to put the code since i'm using object classes
to access the tables, what i can tell you is that on the click event of the
save button i'm calling routines that populate and save the new record to the
table.

 

[Dirk Goldgar]

hi there, i'm working with MDB files, but the tables are linked. Both
forms are unbound and it will hard to put the code since i'm using
object classes to access the tables, what i can tell you is that on
the click event of the save button i'm calling routines that populate
and save the new record to the table.

Hmm. Are these object classes using separate connections to the
back-end database? I'm speculating that there's a latency period
between two connections, and one connection doesn't yet know what the
other connection has done. If this is the case, there may be steps you
can take to refresh whatever cache may be involved; possibly executing
the statement

dbEngine.Idle dbRefreshCache

but maybe something I can't guess at without knowing more about the code
you've written.

 

[Guest]

I don't know what do you mean by separate connections, my guess is not since
there's nothing special on the class modules. what i do is i run a piece code
when the application opens that set the connection string (which is saved on
a varable named cnn)telling the directory where the mdb file with tables is
(this can be the same directory of the application mdb or it can be another
one) then everytime that i need to open a table i use the cnn variable on the
open statement for the recordset.

I'm assuming that the statement that you refer to will force access to flush
the cache. Can you give and example of the sintax.

 

[Dirk Goldgar]

I don't know what do you mean by separate connections, my guess is
not since there's nothing special on the class modules. what i do is
i run a piece code when the application opens that set the connection
string (which is saved on a varable named cnn)telling the directory
where the mdb file with tables is (this can be the same directory of
the application mdb or it can be another one) then everytime that i
need to open a table i use the cnn variable on the open statement for
the recordset.

I think you've just stated the source of your problem. Questions and
speculations:

1. Are you saving just the connection string, not opening a global
Connection object? My guess is, that means every time you use that
connection string to open a recordset, you're creating a separate
Connection object. That could lead to synchronization issues between
the connections, it seems to me, as well as probably taking more time to
create and destroy a Connection object (behind the scenes) for each
recordset. If I were going to take this approach, I'd use a global
Connection object that I'd open at startup and close at shutdown.

2. Does your application MDB file have linked tables to the data MDB?
Are the linked tables used as the recordsources for forms and subforms,
or rowsources for combo boxes? If so, Access will certainly be
maintaining one connection to the data MDB, while any update you run
using an ADO Recordset or Command object involving that connection
string of yours will be using another connection entirely. I wouldn't
be at all surprised to find synchronization and latency problems coming
up here. Maybe calling "DBEngine.Idle dbRefreshCache" will fix that, I
don't know.

3. On the other hand, you could be opening ADO recordsets and assigning
them to the Recordset properties of forms and combo boxes. I would
think these would be read-only, but I can't remember for sure. Are you
doing all your work with unbound forms?

3. Why have you chosen to handle your tables this way? You're working
around Access's natural features, so I assume you have a reason. What
are you gaining?

I'm assuming that the statement that you refer to will force access
to flush the cache.

That's the idea. It's supposed to, but I don't know enough about what
you're doing to say if it will work in this case..

Can you give and example of the sintax.

I already did (except that I miscapitalized: the object is DBEngine, not
dbEngine) -- that's about all there is to it. You can find it in the
DAO help file, or just type it into a code window, click on the keyword
"Idle", and press F1.

 

[Guest]

ok now were talking although i don't quite understnad all of it, so here are
parts of the code:
Public cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\AppData\AppData.MDB;"

the above code is part of what it runs when the application opens.

the following code is what i use to open the tables

Dim rst As ADODB.Recordset

On Error GoTo HandleErrors

Set rst = New ADODB.Recordset
rst.Open "Select * from tblLocations WHERE ID = " & Me.ID, cnn, &
adOpenKeyset, adLockPessimistic


in response to your second point all the tables in the application mdb are
linked to the data mdb. The tables are not the recordsources for the forms,
they are the recordsources for the combo boxes though.

and yes all the forms are unbound.

how can i use a global connection object.

 

[Dirk Goldgar]

ok now were talking although i don't quite understnad all of it, so
here are parts of the code:
Public cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\AppData\AppData.MDB;"

the above code is part of what it runs when the application opens.

the following code is what i use to open the tables

Dim rst As ADODB.Recordset

On Error GoTo HandleErrors

Set rst = New ADODB.Recordset
rst.Open "Select * from tblLocations WHERE ID = " & Me.ID, cnn, &
adOpenKeyset, adLockPessimistic


in response to your second point all the tables in the application
mdb are linked to the data mdb. The tables are not the recordsources
for the forms, they are the recordsources for the combo boxes though.

and yes all the forms are unbound.

how can i use a global connection object.

You *are* using a global Connection object. You said "connection
string" before, which is not the same thing at all, but I see from the
code you posted above that you are actually creating and opening a
Connection object. So that concern is dealt with, leaving us with the
fact that you and Access aren't using the same connection to the
back-end database.

Did you try "DBEngine.Idle dbRefreshCache" yet? Any effect?

You didn't answer my last question: why? Why do it this elaborate and
cumbersome way? Why don't you want to work with the linked tables?
Answers to these questions would really help me to advise you,
especially since we're moving into an area where I have little
knowledge, just manuals and help files to go on. I know DAO pretty
well, ADO only slightly. If your back-end database is an MDB file, why
use ADO at all? DAO would be more efficient. Are you using ADO in
anticipation of shifting the back-end to SQL Server or some other
client-server database? Jet and DAO do a pretty good job at handling
linked ODBC tables.

I'm signing off for the night. Maybe more information will make better
sense of the whole thing, in the morning.

 

[Brendan Reynolds]

Access is already maintaining a global connection for you, which you can
access using CurrentProject.Connection. As Dirk has said, the fact that you
are using one global connection while Access is using a different one may
well be the cause of your problem. My advice would be to dispense with your
global connection and use the one that Access provides. I can't see that you
gain anything from having two connections instead of one. At least give it a
shot and see if it does in fact solve the problem. Just replace the
reference to 'cnn' in your code with 'CurrentProject.Connection' and see
what happens.
--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guest]

sorry for the confusion but i'm new on this stuff i've alway use access just
as a reporting front end, this is the first time that i'm developing a
complete application with it. I trie the dbengine.idle statement and didn't
work. The reason that i'm using unbound forms is because before i started the
application i started reading a book about programming in access (specially
creating multiuser applications, which is what i need), one of the advices
that the authors give on the book is working with unbound forms, so i start
playing with them and at the beginig i like them (mainly because of the
freedom that they gave on using a single form to edit or add data to the
table without the need of specifying in which mode to open the form, or in
the case of edit mode always need to deal with the 1st record of the table)
but as i was working it started to get a litle more complicated that i
excpected (for example the sql commands to open the recordset did'nt work
properly that's why i'm dealing with the connection object, at least that was
the only solution that i could come up with), still i would like to keep the
application as it is since the users started to use it and they like the way
it's currently working. they just ask me to add the functionality of be able
to add records to an lookup table which is where i'm stuck on. Also one of
the other recommendation on the book was to move away from DAO and start
using ADO.