Form doesn't show Records from Table

[JoeMicro]

Hi,

i have a form with a sub-form on it.
with a click of a button Records form a record table should be appended thru an action query into another empty table which is tied to the sub-form, so normally when i open the form i should see all the records from the table,

but the problem is i can't, the sub-form is empty....

but when i open the table or the sub-form (not the main form that carries the sub-form) i do see all the records.

the only time it works is when i enter records in the sub-form thru the main form and append it to the record table. then i could get beck thus records with the action query.
so i tried copying and apsting from the record table onto the form, and appand that way, but it didnt work..

i m working with office 2007

any ideas ?
Joe

 

[CautionMP]

JoeMicro,
After the 'click of a button' if you shift the focus to the sub-form and SHIFT + F9 (requery) do you see the records from the table?

If so you may want to call the [tt]Requery[/tt] method of the subform as the last step in your button click event.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[JoeMicro]

Hi CMP,

thanks for your reply i tried what you suggested SHIFT + F9
the subform gives a blink and stays empty.

here is what the click of a button does

 Select Case Me.Frame7
    Case 1
        Dim QueryName, FormName As String
    
        Dim strTable As String
        Dim rsReceipts As Recordset
        Dim strSQL As String
        'Bring all the records to the form
        QueryName = "To SubRecords"
        DoCmd.OpenQuery QueryName, acNormal, acEdit
        'Open record set to enter receipt caps on the form
        strTable = "Receipts"
        strSQL = "SELECT * FROM  " & strTable & " where [ReceiptNo] = " & txtrcptNo & ""
        Set rsReceipts = CurrentDb.OpenRecordset(strSQL)
        'Check if Receipt exist
        If (rsReceipts.RecordCount = 0) Then
            'If not notify and close
            MsgBox "Receipt " & txtrcptNo & " Not Found!!!"
            GoTo Exit_Command2_Click
        End If
        'if yes, open form
        FormName = "Trade Receipt"
        DoCmd.OpenForm FormName, acNormal
        '& enter receipt caps on form
        [Forms]![Trade Receipt]![txtReceiptNo] = txtrcptNo.Value
        [Forms]![Trade Receipt]![txtCustID].Value = rsReceipts("CustID")
        [Forms]![Trade Receipt]![txtReceiptDate].Value = rsReceipts("ReceiptDate")
        [Forms]![Trade Receipt]![txtMemo].Value = rsReceipts("Memo")
        rsReceipts.Close

but the funny thing s that when i enter first records on the form and then i do this process, then i do see the records on the subform.

all the records in the table i m having trouble with was imported from excel.

Thanks again
Joe

 

[JoeMicro]

Does anybody have any idea???

its interesting, because i have a report and another subform which is tied to the same table, and there i could see the records, and i have a problem only with this 1,

Joe

 

[CautionMP]

JoeMicro,
From your original post and the code block you provided I'm having a hard time visualizing your setup.

but the funny thing s that when i enter first records on the form and then i do this process, then i do see the records on the subform.

This makes me thing the culprit may be the link Master/Child relationship between the form and subform. If the following code

[tt]...
'& enter receipt caps on form
[Forms]![Trade Receipt]![txtReceiptNo] = txtrcptNo.Value
[Forms]![Trade Receipt]![txtCustID].Value = rsReceipts("CustID")
[Forms]![Trade Receipt]![txtReceiptDate].Value = rsReceipts("ReceiptDate")
[Forms]![Trade Receipt]![txtMemo].Value = rsReceipts("Memo")
...[/tt]

is creating a new record on the main form, and the link Master is one of the four fields being populated by the code, the subform won't show the related records until the new record in the main form is saved.

Just a thought,
CMP


[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[JoeMicro]

Hi CMP,

the Fields on the main form are "Unbound" and the code is only filling them with previously recorded records (actually also imported from Excel)

Joe

 

[JoeMicro]

Thanks everybody for the great help!

i just deleted and redid the subform on the main form and now it works ok (sofar)

Joe