Refresh combo box and form select list.

[IfButOnly]

I have a form which has a select list as it's record source and am using a combo box on the form, with a select list behind it, to select a specific record and go to it to fill the form. This all works okay until I create a new record.

Basically I open a form to enter Client info and another to enter site info (it is a many to many relationship). I then use an INSERT INTO to update the index file and close the update forms. When I come back to the main form, I cannot get the form or combo boxes to refresh and show the updated records - I need to close the main form and restart to get to them.

What am I missing?

Any suggestions would be very much appreciated.

 

[llkhoutx]

Requery the form and combo box after update.

 

[IfButOnly]

This is what I have been doing, but it doesnt make any difference. To update the index file, I use an INSERT INTO statement which appears to update immediately. The normal form close ( and docmd.close .... acSaveYes) with requery after is the problem.

It could be to do with the position of the requery - I notice sometimes if I have a docmd.openform... and some following statements - it seems to execute the following statements before passing control to the new opened form??? My expectation is that execution of the main form code halts at the openform command until the form is closed - I suspect my expectations are suspect - are you aware of anything different in this area. It would explain why my refreshes don't appear to take effect.

Thanks

 

[llkhoutx]

If the main form stays open, you've got to requery the main form and the combo box if on the main form when you go back there (on event OnClose of the data entry forms). You added data, but the main form and the combo box on it don't know until you requery.

I think the foregoing fixes your problem. Alternatively,

Go to the recordsource property of the combo box in design view after you have added a new record via your "INSERT ..." and see if the added data displays. If it's not there, check the actual table/query which is the recordsource for the combo box to see if it's there.

If the data is in either of the places, you're just not requering on the proper event.

I can't tell exactly what your doing. If you're inserting on the NotInList event of the combo box, it's a another problem. Responce=acDataErrAdded ius not being set.

 

[IfButOnly]

thanks for your help to date - I am working through the form to make sure I am requerying everything - the form contains a number of combo boxes, list boxes and a subform with more of the same.

I have attached a portion of my code below which is a procedure in the subform - this may not be directly related to the requery problem as generally I find that the 'currentdb.exe method' does an immediate update that is reflected in my combo/list boxes.

This procedure calls a pop-up form that has a simple option box to determine what action to take. When the DeleteContact button is pressed, the popup and the msgbox is displayed at the same time and I need to respond to the msgbox before access to the popup. As you see from the code the msgbox is down the bottom of the procedure - ergo the whole procedure is actioned, including the requery, before I get to the openform. In this particular case the delete actions are not taken because the code is evaluated as zero before the popup resets it - it appears that when I close the popup form it continues from the bottom of the procedure.

Is this the way Access is supposed to work or do I need to do something specific to halt execution of the main code until I return from the openform command?

'------------------------------------------------------
Private Sub btnDeleteContact_Click()
' procedure called to delete client or remove the client/site link

gintDeleteAction = 0 ' initialise global var

DoCmd.OpenForm "pupDeleteContactCheck" ' delete contact or remove link?

If gintDeleteAction = 1 Then ' remove the link
strSQL = "DELETE * FROM [Linker] WHERE [SiteID] = " & Me.SiteID & _
" AND [ContactID] = " & Me.lstSiteContacts.Column(1)
CurrentDb.Execute strSQL
End If

If gintDeleteAction = 2 Then ' delete contact & remove link
' remove the link
strSQL = "DELETE * FROM [Linker] WHERE [SiteID] = " & Me.SiteID & _
" AND [ContactID] = " & Me.lstSiteContacts.Column(1)
CurrentDb.Execute strSQL
' now delete the record from the Contacts table.
strSQL = "DELETE * FROM [tblContacts] " & _
"WHERE [ContactID] = " & Me.lstSiteContacts.Column(1)
CurrentDb.Execute strSQL
End If

MsgBox gintDeleteAction

Me.lstSiteContacts.Requery
Me.Requery

End Sub
'----------------------------------------

many thanks.

 

[llkhoutx]

There might be a problem with the "Me." When taking action on the form use "me.", however, when referencing objects on the form use "me!". You always use "me.".

Check each "strsql" in the qbe frame to see if they work. Use a breakpoint to trap the strsql, copy it, and then paste it in the QBE frame in the SQL view.

When you say "select list" I assume that you me a "table/query" rowsource type for your combo box.

If a "select list" (as you call it) is appended while the form containing the subject combo box is open, you have to requery the combo box before the appended records will be seen.

The trouble with forms with several subforms, combo boxes and list boxes is that each has to be requeried after the form filter or recordsource is modified, whether by a DELETE or an APPEND.

 

[IfButOnly]

I will fix up the .me and !me's. The SQL is fine - I always check it in a query.

I may not have stated my main concern clearly.

If I have an "OpenForm" statement at the top of a procedure, it appears to execute all following commands in the procedure before the OpenForm. After the Openform event is closed, it goes directly to the end of the procedure ignoring the commands following the OpenForm command.

therefore my requeries are being executed before I update the tables and not after I affect changes to the tables.

I have triple checked this with msgbox's everywhere and am convinced this is happening as stated above. As I said in my earlier reply - I would expect execution to halt at the OpenForm statement until the form is closed. Am I misunderstanding how Access works, missing a Reference object, or??

thanks for all your feedback so far - hope you can help with this one.

 

[boblarson]

Try getting rid of the ME references and call out the form you want to requery explicitly! Try that first and then if that doesn't work then there's a problem. However, I think that, by looking at your code, that the main form does not receive the focus back and as such is no longer "ME" which references the currently active object.

 

[llkhoutx]

When you have an openform at the top of a procedure, rest assured that all code following the openform is executed. View the code in design view and set a breakpoint (F9) on the first line following the openform, close and execute. You'll see that all your code is being executed. Code runs through completion and does not stop, just because another form is opened.

You need to do the requeries after the INSERT is executed.

After you execute your INSERT, you have to requery all objects using the table the subject of the INSERT.

Me, after an openform still references the form who's code is being executed. All code is executed, regardless.

 

[boblarson]

I would still use explicit references at first to make sure it's working and then substitute and see if that changes anything.

Just a suggestion as I've been burned before with this same issue.

I also just noticed that you are opening the other form, but unless I'm missing something, I don't see where the code would give you the chance to enter anything within that form to be able to get your 1 or 2 value to be able to have it evaluate the expression that you have after the open form code. I haven't tried it, but maybe you are doing this by opening the form in modal popup mode. Other than that I would think that the code is being executed prior to being able to make a selection. But like I said I may be missing something.

 

[IfButOnly]

I am working at ensuring I have all recommendations made to the code. However, I still do not understand the issue with the openform.

the openform command opens a modal popup form which sets the global variable gint.... I have also put a msgbox (say msgbox2) in the popup form, followed by a docmd.close.

the sequence I see on entering the procedure is:- msgbox1 (from bottom of main procedure) and the pop up form are displayed - I need to click the msgbox before the popup form receives attention. I do that and then made a selection in the popup form, and msgbox2 is displayed. The code following the openform statement is not executed again as msgbox1 is not redisplayed.

Is the fact that I am using a modal popup form significant?

 

[dgm]

Peter,

The code you are using opens the form and then continues to execute the rest of the code in that procedure, therefore your requeries and other code is being executed too early and will not execute again.
If you change:

DoCmd.OpenForm "pupDeleteContactCheck"

to

DoCmd.OpenForm "pupDeleteContactCheck", , , , , acDialog

the code will stop once the form is open and continue when you close the form, which I believe is what you want to happen.

Alternatively, move all the code below the openform to the close event of "pupDeleteContactCheck" and change the me.controlname references to the forms!formname!controlname style.

Dave

 

[IfButOnly]

Thanks Dave,

The "OpenForm,,,,,acDialog" was spot on - everything done in the right sequence and listbox refreshed.

Many thanks also to IIkhoutx and boblarson for your time and input.

Peter.