updating a form with new values

[markjkubicki]

I have a form (with several controls: checkboxes, option buttons...), that displays only (1) record, and gets it's data from a table [tbe...] that also has only (1) record

on my form is an option button frame that lets the user select (1) of several records in a second table [tbl...]
the record in the first table [tbe...] is then replaced with the user's choice from the 2nd [tbl...]

...all of this works fine, but what I can't get to work, is an action that updates the controls on the form

I also tried moving the code: "me.requery" to the after update event; that did not produce favorable results either...

many thanks in advance,
mark




here's the code:
---------------------------------------

Private Sub frmPresetOption_beforeUpdate(Cancel As Integer)


Dim sSQL As String
DoCmd.SetWarnings False

sSQL = "Delete * from [tbeFixtureSchedulePrintOptions];"
DoCmd.RunSQL sSQL

sSQL = "INSERT INTO tbeFixtureSchedulePrintOptions" _
& " SELECT *" _
& " from tblFixtureSchedulePrintOptions" _
& " WHERE [PresetOption] = " & Me.frmPresetOption & ";"
DoCmd.RunSQL sSQL

Me.Requery
DoCmd.SetWarnings True

[NTC]

did you try me.refresh?

[markjkubicki]

just tried me. refresh, and it wasn't the answer

then I tried moving the me.refresh from the beforeUpdate to afterUpdate
it's closer to being right, but the form updated to null values -as if the added record hadn't arrived yet... (timing?)

[pkstormy]

Try me.repaint

This refreshes the form with changes of caption boxes and other controls on the form.

[markjkubicki]

hmpft!, now i'm really stumped

at least .refresh gave me a form with null values;
.repaint, seems to have no action (this makes no sense to me, and i wonder if i ought to be looking for some other "issue" ? )

[pkstormy]

me.repaint doesn't refresh your text box values, only things like caption box changes and non data value controls.

[markjkubicki]

the action I'm having trouble with is:

form: Main Menu
tab: Reports
command button: Additional print Options
form: Fixtrue Schedule Print Options
option button group: Preset schedule Options (top left side of menu)

[weekend00]

base on my understanding, PkstormY were in the wrong way of what Mark wants.

Mark:

Since you delete all the records in the table (actually only one record) as following code, for sure you get all nulls values on the form afte refresh because the form is link to the table. Nothing in the table leads to nulls in the form.

sSQL = "Delete * from [tbeFixtureSchedulePrintOptions];"
DoCmd.RunSQL sSQL

actully, you don't need any code to accomplish that. create a combo box in you form with data from your second table, set control source to the field of the first table. then that's it. when ever the user change the selection in the combo box, it save to the first table.

[markjkubicki]

not sure how all of the other fields in the first table will be updated, but let me play with it for a while...

(BTW, while choosing a value in the combo box or the option group will set the default values for the remainder of the table; the user will still needs to have the right ot override the defaults at any time; these overrides are then stored in the first table (the default values aree in the 2nd)

[weekend00]

If you want the user has the right to type in any value, please set the combo box property "Limit to list" to false.

[markjkubicki]

(the combo box would be the one control on the form that i do not want the user to have cchoice (these are only to be recommended setting for the options on the form (its other controls), and those preset options are being determined by a higher power :-)

[weekend00]

I am confused.

you can work on you original codes as you first post, but don't use update instead of delete.,

[markjkubicki]

hmmm "update" that's new command for me, I'll play with it later today

(BTW, you did notice that there is a line of code after the line that deletes a record to then add a record. the me.refresh command happens after that -so wouldn't the form be refreshing to the recently added record, not the state inbetween where there is none?.

sSQL = "Delete * from [tbeFixtureSchedulePrintOptions];"
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

sSQL = "INSERT INTO tbeFixtureSchedulePrintOptions" _
& " SELECT *" _
& " from tblFixtureSchedulePrintOptions" _
& " WHERE [PresetOption] = " & Me.frmPresetOption & ";"
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

'Me.Refresh

[weekend00]

but what you added was base on the table which you just deleted all records, hence there must be nothing you can get.

[markjkubicki]

that shouldn't be true
I am inserting from the table TBE... (based on the selection of the option group) from the table tbL... the records from tbL... ought to never be delted, only those from tbE...

did I code something backwards?