Object invalid error with temporary table 1

[NeedsHelp101]

Hi,
I'm very new to VBA and I was hoping someone could help:

I'm running a For Each loop through values stored in the temporary table (working) and storing them into a permanent table (tblTrafficForForm).
The working table is populated from the form, and three columns (open, close, and market) in the permanent table are populated from two textboxes, and a multiselect list box (lstMarket), respectively, in the form.
The goal is to assign all the data in the working table to each open-close-market combination.

Dim dbMarketing As Database
Dim FMarket As Variant
Dim rec As RecordSet
Dim working


Set dbMarketing = CurrentDb()
Set rec = dbMarketing.OpenRecordset("tblTrafficForForm")
Set working = dbMarketing.OpenRecordset("working")


working.MoveFirst




For Each FMarket In Me.lstMarket.ItemsSelected
    rec.AddNew
    
    [b]'Not sure if referencing is correct[/b]
    rec!Col1 = Me.boxCol1.Value
    rec!Col2 = Me.boxCol2.Value
    rec!market = Me.lstMarket.ItemData(FMarket)
    rec!Col4 = working.boxCol4.Value
    rec!Col5 = working.lstCol5.Value
    rec!Col6 = working.lstCol6.Value
    rec!Col7 = working.lstCol7.Value
    rec!Col8 = working.lstCol8.Value
    rec!Col9 = working.txtCol9.Value
    rec!Col10 = working.lstCol10.Value
 
    rec.Update [b]'Here i get the error: Object invalid or no longer set[/b]
    
 Next FMarket
    rec.Close

   
working.MoveNext
'I used Col4 here bc there should always be a value in it
If IsNull(working!Col4) Then
    working.Clear [b]'is this correct to empty the table?[/b]
    [b] 'Is there a way to refresh the subform so it can be reused for entry of the next data set?[/b]
    Else: GoTo begin
End If



Set rec = Nothing
Set dbMarketing = Nothing
Set working = Nothing

I bolded the problems I'm having with the code. Any help would be greatly appreciated!!

 

[Remou]

Shouldn't these:
rec!Col4 = working.boxCol4.Value
Read:
rec!Col4 = working!boxCol4

 

[NeedsHelp101]

Is there a difference between (!) and (.) ?
Anyhow, I changed all of them and still get the "Object Invalid or No Longer Set" error on rec.AddNew ...

 

[Remou]

There is a difference, especially when dealing with recordsets. What you have above is two recordsets, working and tblTrafficForForm, from two tables. When I test on this assumption, the code works quite well, except that Col4 is not a field in the working recordset and Working.Clear will cause an error. Perhaps if you say what you wish to do, we can find what the problem is?

 

[NeedsHelp101]

Working consists of the fields named "Col 4 - 10" and tblTrafficForForm consists of Col1, Col2, market, and Cols 4-10.

What should happen in the form is that the user enters values for Col1, Col2, and selects several markets. In a subform, the user enters Cols 4-10, which may contain several records. I need this loop to attach the entries in Cols 4-10 for each market selected. Cols 1 and 2 will contain start and end dates, and will also appear next to each market selected.

After the selections are made and the program is run, I need working table to clear itself, so that the user can start the process again, making new and different market selections. (These will then be appended to the tblTrafficForForm table).

Does that help?

 

[Remou]

So working is the table for the subform, is it? And you want to end up with:

tblTrafficForForm
Date Date Market1 Cols4 - 10 (i)
Date Date Market1 Cols4 - 10 (ii)
Date Date Market1 Cols4 - 10 (iii)
Date Date Market2 Cols4 - 10 (i)
Date Date Market2 Cols4 - 10 (ii)
Date Date Market2 Cols4 - 10 (iii)
<etc...>

And then blank the subform and the table on which it is based?

 

[NeedsHelp101]

Yes, exactly right - and blanking it is ok because it will all be written into tblTrafficForForm.
(Sorry it took me so long to reply).
What do you think?

 

[Remou]

Ok, try this. I have guessed a lot of names, so you will need to fiddle around:

Private Sub cmdSave_Click()
Dim FMarket As Variant
Dim strSQL As String

If Me.lstMarket.ItemsSelected.Count = 0 Then
   MsgBox "Nothing to do"
   Exit Sub
End If

If Trim(Me.boxcol1 & "") = "" Or Trim(Me.boxcol1 & "") = "" Then
   MsgBox "No dates"
   Exit Sub
End If

DoCmd.SetWarnings False
For Each FMarket In Me.lstMarket.ItemsSelected
    strSQL = "INSERT INTO tblTrafficForForm " _
    & "( Col1, Col2, market, Col4, Col5, Col6, Col7, Col8, Col9, Col10 ) " _
    & "SELECT #" & Me.boxcol1 & "#, #" & Me.boxcol2 & "#, '" & FMarket & "', " _
    & "working.Col4, working.Col5, working.Col6, working.Col7, working.Col8, " _
    & "working.Col9, working.Col10 " _
    & "FROM working"
    DoCmd.RunSQL strSQL
Next FMarket

strSQL = "Delete * From Working"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Me.[Working subform].Requery
End Sub

 

[NeedsHelp101]

Thanks! Your code helped me see what I was doing wrong.
So to refresh the subform, I have to do a .Requery, correct?
And to clear it, is there any other way to do it besides putting everything into strSQL? ( I didn't end up doing that in my code, so I was hoping to find another way except this "Delete *" part).

Thanks again and have a good weekend!

 

[Remou]

Yes, but the SQL would be a common way, what do you dislike about it? I do not think any of this is safe for a multi-user environment, so what else?

 

[NeedsHelp101]

I don't dislike it, I'm just more familiar with things that kind of look like Java. There's no other command that will clear the table then?
Sorry!

 

[Remou]

Well I guess you could use the recordset, but it is much slower, as far as I know:

Dim rs As DAO.Recordset
Set rs = Me.[working subform].Form.RecordsetClone
rs.MoveFirst
Do While Not rs.EOF
rs.Delete
rs.MoveNext
Loop
rs.Close

Me.[Working subform].Requery

 

[NeedsHelp101]

I'm having problems with the Me.[working subform] part...
I was just typing in the name of the subform as I'd saved it, but VBA is saying method or data member not found. This problem occurs in both the SQL code and recordset code versions. What could it be?

 

[Remou]

Ah, I should have said, working subform control name. While the saved subform and the subform control often have the same name, it is not always the case.

To labour the point, what you need is either the name that intellisense pops up when you type Me., often something like working_subform, or else the name of the control that contains the form, that is the control with Link Master and Link Child properties.