Update form record source & accde file

[snoopy2003]

Hello,



I have a form with dynamic record source.
I wrote a code to update the record source when it is opened:

DoCmd.OpenForm "frmUpdateList", acDesign

mySQL = "SELECT * from " & listName
Forms!frmUpdateList.Form.RecordSource = mySQL

Forms!frmUpdateList!title.Caption = title
Forms!frmUpdateList!listData.controlSource = controlSource
'Forms!frmUpdateList.Requery

DoCmd.Close acForm, "frmUpdateList", acSaveYes

DoCmd.OpenForm "frmUpdateList", , , , , acDialog

The code is working properly in accdb/accdr files, but in accde
file I get an error 7802 the command you specified is not available in accde database.

Is there an alternative way of updating the record source ?

Thank you for your help

[ajetrumpet]

the first line is the one that's in error. accde's are executables, hence no opening in design view can be done.

but you do not need to open a form in design view in order to change it's recordsource property. so you can eliminate that line altogether and go with the alternative, which of course would be to simply open it and change it.

[snoopy2003]

Hello,

Thank you for your help.

When the form is opened I wish to:
1. Update form source
2. Halt code execution (until popup form will be closed).

If I open the form using DoCmd.OpenForm "frmUpdateList", , , , , acDialog I can not update the form source because code execution is halted

If I open the form using DoCmd.OpenForm "frmUpdateList" I can not halt code exception. Because of this reason I opened the code in design, saved it and opened it again using Dialog.

Do you have and suggestion how to halt the code afte ropen the form DoCmd.OpenForm "frmUpdateList" ?

Thank you for your help

[ajetrumpet]

theres a lot of ways you can halt code execution, but if you're using an executable file like an accde, your best bet is going to be using a message box or something like that. why do you want to halt it again?

I'm sorry, but I did not really understand anything of your last post.

[khalid]

Moreover the accde is an executable front end user file with all the codes and design tools removed or locked, so no one can open a form for designing.

The following line of code is also suspicious:

Forms!frmUpdateList!title.Caption = title
should be....
Forms!frmUpdateList!title.Caption = "title"

[snoopy2003]

Hello,

Thank you help.

I have a some combo boxes in a form.

Each combobox gets its values from a different table.

In case the user does not find appropriate value in the combobox list he
double click on the combobox, a popup form is opened and the user can update the relevant table values (continues form).

The popup form title and record source is updated according to the relevant combox which was clicked.

After the user closes the popup form - he can choose the new value in the combobox.

I need to halt the code untill the user close the popup form because I requery combobox using VBA after the user finished add/update values in the popup form.

When I used acDesign (which is not a preferred way) I opened the form, update its properties - title, source), save & close and opened it again as acDialog. Now - I open it normally, update its properties and wish to halt
execution of the code.

Can you help me ?

Thank you

msgbox is not a suitable solution for me because I wish that the popup form will remain in focus.

[khalid]

Hello,

When I used acDesign (which is not a preferred way) I opened the form, update its properties - title, source), save & close and opened it again as acDialog. Now - I open it normally, update its properties and wish to halt
execution of the code.

Can you help me ?

Thank you

msgbox is not a suitable solution for me because I wish that the popup form will remain in focus.

it seems that you want to change the TITLE of the form to some new TEXT, perhaps you want to change it to the Combo Box's Value????

If this is the case you can do that by getting the Combo box Column's value to your Form's Title.

Me.YourFormTitle.Value= Me.YourCombo.Column(n)

Hope this helps.

[snoopy2003]

Hello,

Thank your help.

Form's title is modified according to the form source.
I mean: if a user double click on a combo box "city", the form will be opened and will display cities values from an appropriate table. The form title will be changed to "Cities Values". Anyway, this works in executable file.

My problem is to update combo box values (requery combo box record source) after the pop up form is closed.

Can I update form record source without opening it ?

Thank you for your help

[khalid]

What is the After_Update code of your combo box??? can you paste it here??

Just add this line of code to your combo box at the end of After_Update code:

Me.YourCombo.Requery

This will requery the combo box data:

[snoopy2003]

Hello,

Thank you for your help.

The after update event did not work, but your suggestion was great.
I finally requery the combobox when the 'close' command button was clicked in my update form.

forms([UpdateThisParentForm])([UpdateThisSubFormOnParentForm]).form([UpdateThisField]).requery

Now it works correctly and there is no need to open the pop up form is design mode.


Thank you for your support

[khalid]

Great!
That it works for you