SubForm

[siebel2002]

I have a sub form that is linked to main form via FirmID.

When a firm is selected from a drop down box in the main form, I have a table (tblFirm) that is built dynamically that pulls in all the relevant pieces of data from a previously saved Table (tblFirmEntered) .

The controls in the subform ( I have based the subform on tblFirm) needs to be populated with values from tblFirmEntered. The main form is based on tblFirmEntered. It is not Happpening!

Here is the code:

strSQL = "Delete * From tblFirm"
CurrentDb.Execute strSQL
Me.subFormTest.Requery
strSQL = "Insert into tblFirm (FirmID,FirmName,AccountManager,InternetAddress,EMail) " & _"Select FirmID,FirmName,AccountManager,InternetAddress,EMail" & _
"From tblFirmEntered Where tblFirmEntered.FirmID = '" & strFirmID & "'"
CurrentDb.Execute strSQL
Me.subFormTest.Requery

The code runs ok, but the controls FirmName, etc remain blank!

Can anyone offer any suggestion?

Thanks!

 

[Aristarco]

Have you tested de Refresh command? It seems to me that you're making a requery using the strSQL, but nor telling the for that the data changed. Maybe using the refresh, or even closing then reopnening. To boldly code, where no programmer has compiled before!

 

[siebel2002]

Yes I have.. As you know, you can only use the Requery method on the subForm. Still no luck!..

 

[HowieG]

It may be just the way you retyped it here, but there isn't a space between the last field and the FROM keyword:

...,AccountManager,InternetAddress,EMail" & _
"From tblFirmEntered...

I end up doing this all the time with SQL strings in my code.

 

[siebel2002]

Hi Howie:

The space issue is ok in the original code..still no luck

 

[HowieG]

Let's try to narrow this down to a sql problem or a forms problem.

You are deleting all records from tblFirm, then adding a record to it for the one you're trying to look up. Does the table get emptied like you'd expect from the first sql statement? Does the appropriate record get added into it based on the second sql statement? You probably want to stop the code and manually check the table after each sql command.

If yes to both of those questions, then it's a form problem. Assuming that to be the case, what are you using for your recordsource and/or filter for the form? What field(s) are you using for the parent/child linking?

Have you tried a requery on the parent form (just a thought)?