Cannot get form to show records added through ADO

[RCurtin]

Hi,
I'm having problems getting a form to show records that I've just added through ADO.

The following code is in a module. What I'm doing is creating a new transmittal record (like a delivery note) and a drawing record. The code to add the records works perfectly - they are there when I look in the tables but when I open the form it doesn't have the new records.

Please respond with any ideas - I've spent ages trying to get this to work or find some way around it.

 With transmittalsRecords
    If IsNull(openTransmittal) = True Then
        
       [COLOR="YellowGreen"] 'create new transmittal note (one side of relationship)[/COLOR]        
        .AddNew
        !DocTypeA = ColA
        !DocTypeB = ColB
        !DocTypeC = ColC
        !DocTypeSeq = ColSeq
        !TransmittalDate = Date
        !CCNum = ControlCopy
        newTransID = !DocID
        .Update
    End If
   [COLOR="yellowgreen"] 'add new drawing (one side of relationship)[/COLOR]
    .AddNew
    !outTransmittalID = newTransID
    !DrawingNum = DrawingNum
    !RevisionNum = Rev
    !ActionCode = previousActionCode
    !quantity = 1
    
    .Update
    .MoveNext
End With
    
Debug.Print "newTransID: " & newTransID

[COLOR="yellowgreen"]'my attempts to get it to requery..[/COLOR]
[COLOR="Red"]Forms!frmOutTransmittals.Form.RecordSource = "qselOutTransmittal"
Forms!frmOutTransmittals.Form.Requery
Forms!frmOutTransmittals.Form.Refresh

'open form to show transmittal just added
DoCmd.OpenForm "frmOutTransmittals", acNormal, , "[DocID]=" & newTransID & ""[/COLOR]
End If

 

[RCurtin]

Thanks for your reply Pat.

However, this code is running from a module and also it is run before the form has been opened. So how do I use recordsetClone? My understanding was that recordsetclone created a copy of the underlying query?

(I don't understand why the new records don't just appear - if I open the tables themselves the new records are there.)

By the way the I'm not using a listbox or combobox - was expecting the new records to show up in the form itself.

 

[RCurtin]

Hi Pat,
Thanks again for your reply.

The form is not filtered and there is no where query in the recordsource query. I have tried the openForm method without the where clause also:

DoCmd.OpenForm "frmOutTransmittals", acNormal

and it does not show the 2 new records just added.

I have checked if the autonumber returned is correct and it is - I printed them to the debug window and checked in the table to see that they were the same.

 

[KeithG]

Does the record show up if you run the underlying query of the form?

 

[RCurtin]

Hi,
I'm sorry I haven't replied to this before now - I was working on a different project.
The issue is that records added through code do not show up when the form is opened.

Just to recap/confirm:
- the records are added propperly through the code
- I can see the records that have been added if I look in the 2 tables concerned or in the query
- there is no filter on the form
- there is no Where clause in the recordsource query
- I have changed the OpenForm to open all records:
DoCmd.OpenForm "frmOutTransmittals"


What I have just discovered is that if I put a message box just before the OpenForm line and wait for about 5 seconds, it does work! Otherwise the record just added through code doesn't show up in the form.

Any ideas why this might be and what I can do to fix it??

 

[workmad3]

It could just be the time it takes the objects to refrsh their data. The forms use DAO in their underlying implementation, and those have a refresh time. I would guess that because you updated the data seperate from the forms data connection, the form hasn't bothered refreshing its data because it doesnt think any data has changed.

 

[RCurtin]

Hi,
Thanks for the reply. I did put these lines in the form load event of the form but it didn't seem to do the trick:
Me.Requery
Me.Refresh

I've just forund some code to get it to pause for 5 seconds before opening the form and that works fine. Is there any other way I could do this to avoid this delay?

Would it be better if I coded it in DAO instead of ADO?

 

[workmad3]

I would stick with ADO, to be honest.If anyone asks, you can always say the 5 second delay is a 'loading time' which is fairly accurate

You may be able to get some better performance by tweaking the values in tools|options->advamced tab, which has refresh rate values too, but the defaults may be like that for a reason

 

[RCurtin]

Thanks for that workmad3
I just needed to know I was doing it the best way possible.

 

[RCurtin]

Thanks for that Pat but the code is run from a module before the form is opened so I don't know if I could use RecordSetClone in that case??