Compile Error - Please verify my code (1 Viewer)

[RogueJD]

Hey all,

I have a problem with a query. I'm trying to generate a kind of report. The database is for a warehouse of sorts.

What is supposed to happen is someone Pulls up a Form (Pull_Sheet_Helper). They copy / paste a list of IDs into an unbound field called "isnlist".

From the list, it should generate a spreadsheet of where the ID (ISN) has their property located.

I can't post the DB. It's on a government SIPR computer network (Secret Access only).

I'm having problems with the code. I paste the ID (ISN) and push the command button that starts an onClick event with the code below.

Since I can't post the DB, I'll give you a layout of the related tables and forms:

Master_Table (The... Master table.) Has the following related fields:
ISN
PROPERTY_LOCATION
ALTERNATE_LOCATION
DISPOSITION

The form is called:
Pull_Sheet_Helper

It has an unbound text box called:
isnlist

And a Query called Pull_Sheet_Query

Here is the code for the command button:

Private Sub Run_Query_Click()
 Dim Master_Table As Varaint
 Master_Table = Split(isnlist.Value, vbCrLf)
 Dim ID As Long
 Dim sqls As String
 Dim rs As Recordset
 
 'On Error GoTo newISNerror
 
 Set rs = CurrentDb.OpenRecordset ("Master_Table")
 
 rs.Index = "ISN"
 
 Dim nSQL As String
 
 For Each ISN In Master_Table
  If Len(ISN) = 6 Then
   rs.Seek "=", ISN
   If rs.NoMatch Then
    nSQL = "insert into Master_Table (ISN, PROPERTY_LOCATION) values ('" & ISN & "', 'NOLOC')"
    DoCmd.RunSQL nSQL
   End If
  End If
 Next
 
 For Each ISN in Master_Table
  If Len(sqls) = 0 Then
   sqls = "select ISN, PROPERTY_LOCATION, ' ', ALTERNATE_LOCATION + '/' + DISPOSITION from Master_Table where ISN = '" & ISN & "'"
  Else
   sqls = sqls & " or ISN= '" & ISN & "'"
  End If
 Next
 sqls = sqls & " order by isn ASC;"
sqlbox.Value = sqls
 
 DoCmd.OpenQuery "Pull_Sheet_Query", acViewNormal, acReadOnly
newISNerror:
 'Resume Next
 
End Sub

I'm getting a "Compile Error" in the code of the command button:
"Variable required - can't assign to this expression". When I run the debugger, it highlights this line of the above code:

 For Each [COLOR=black]ISN [/COLOR]in Master_Table

"ISN" Is highlighted.

This was pretty much a copy / paste code from another warehouse's database. I simply changed the table names to match my own. (That may be the problem - not sure if I did it right.)

Can anyone please look at the code, bounce it off of the table names and see what I need to do to correct this error? I'm a beginner at Access, and even worse at vb. Please break it down "Barney Style."

Thanks!

-J. "Jon" Rogue

 

[RogueJD]

There may be some typos in the code. I had to manually re-type it into a NIPR (Unrestricted) computer to post it on this forum.

 

[Zigzag]

Not sure but don't you need somewhere!

Dim ISN as integer or
Dim ISN as long

 

[RogueJD]

Ok - that makes sense - in the first line of code:

Dim ID as Long

That probably referenced to a field in one of the tables from the other warehouse's database.

I changed ID to ISN (Is that right? It needs to reference a field?)

Now, I'm getting a new error, same line is highlighted, "Compile Error: For Each control variable must be a Variant or Object"

I'm sure it's a simple fix - I just don't quite know what I'm looking at when it comes to vb.

 

[Zigzag]

Try this instead.

Dim ISN as Variant

 

[JANR]

rs.Seek "=", ISN

FYI this woulden't work on a Linked table use

rs.FindFirst...

JR

 

[RogueJD]

Ok. I'll make the changes the next time on the SIPR network. It might be a few days, I have to fly out of Baghdad in the morning.

JR, the code was working before - but with a different DB. I'll try the replacement you suggested.

Thanks!

-J. "Jon" Rogue