Continuous form not updating properly?

[sozzer]

I have a continuous subform within a single form. On click of a button on the single form, procedures run which:

i. Bring data into controls on the single form from three tables via ADO (refresh the screen view)

ii. Evaluate that data (various if statements)

iii. Depending on the output of (ii), runs a procedure which inserts data using ADO in a new record in the table behind the continuous form and re-sets the recordsource of the subform:

 Forms.frmBookView!Child749.Form.RecordSource = "SELECT tblDays.*, tblUsers.* FROM tblDays INNER JOIN tblUsers ON tblDays.StaffID = tblUsers.StaffID WHERE tblDays.HolDate = " & MakeUSDate(Forms.frmBookView.Combo487.Value)

This works fine and I see the new record in the subform as long as there is already a record displayed.

However, if it's the first record for the specified date, the subform does not refresh properly.

It is not until i click the button again and insert another record that i see the previously entered record (ie I either see 0 records or 2 or more 2 - never just 1)

If I run the refresh procedure on its own (i) immediately after entering the new record, i sometimes get the updated view in the subform and other times i have to run it twice before i see the record.

This problem persists:

- If Allow Additions is set to Yes or No on the subform
- If there no records or at all in table or if there are no records matching the recordsource criteria of the subform

Is this normal behaviour.......?



As ever, many thanks in advance

 

[BoxHead]

Certainly seems strange that it works unless it's the first record returned by the query.

I'd have to guess that
1.) there is some event that is not firing since there is no data in the subform
a) Does your command button try to shift the focus to the subform at all
b) do you have a subform.requery event running on some other event?

or

2.) the combo487 control is not actually updated and holding the date value when the command button is pressed - leaving the query with no matching records.

I'd try throwing in a nameOfSubform.Requery and see if thst resolves it.

Take a look in Help at the difference between .Requery and .Refresh. That may explain the cause.

HTH






When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[sozzer]

Thanks for your advice.

.Requery and/ or .Refresh make no difference, and there are no events shifting focus to any control/form.

Thought it was a connection prob but same issue persists whatever i do - it's like the SQL runs ahead of the update procedure.

I must be doing something wrong - but surely you would expect a constant prob, not just with the first record?

May have to sort out a workaround but i'd love to know what's going on!

Many thanks