not in list problem

[mariobro]

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.

 

[mariobro]

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.

 

[Ken Snell [MVP]]

After the benefit of another set of eyes looking at your post, it occurs to
us that you may just be opening the form without the right "window mode"
argument.

Namely, if you want the popup form to open and your code to wait for it to
close, you must open that popup form (the modal form) in Dialog mode. This
is the sixth argument in the DoCmd.OpenForm method:

DoCmd.OpenForm "FormName", , , , , acDialog

or you can use this:

DoCmd.OpenForm "FormName", WindowMode:=acDialog

That may be what you need.
--

Ken Snell
<MS ACCESS MVP>

 

[mariobro]

here's the code:

Private Sub Combo0_NotInList(NewData As String, Response As Integer)

Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String

strMsg = NewData & " Is not on File, Do you want to add it?"
mbrResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "Item not Found")

Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "frmLocations", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData

If IsLoaded("frmLocations") Then
DoCmd.Close acForm, "frmLocations"
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
Case vbNo
Response = acDataErrContinue
End Select

End Sub

the problem is not the forms that add the new item, but with the code above,
for whatever reason the combo box source data it's not being requery.

 

[Ken Snell [MVP]]

What controls are on the frmLocations form? How do you "leave" that form
when you're done entering data? Are you clicking a command button on that
form that closes the form? Or does it make the form invisible?

I'm guessing that frmLocations has a button that closes the form. In that
situation, your code never finds that form open (as it was closed), so the
Response variable is never set to acDataErrAdded. If this is correct, change
the code on the "CLOSE" button on the frmLocations form to make the form
invisible instead of closing the form.
--

Ken Snell
<MS ACCESS MVP>

 

[mariobro]

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>

 

[mariobro]

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.

 

[mariobro]

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.

 

[mariobro]

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.

 

[mariobro]

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.

 

[mariobro]

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.

 

[mariobro]

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.