Build loop into a variable (1 Viewer)

[cocowomble]

hi.

Please help, I am relatively new to VBA and have this loop (see below) was wondering if it was possible to build it into a variable, so I could call upon it within an IF statement.

pleas forgive me, if I am completely talking nonsense.

thanks
Mark

Set ctl = Me.listSeeAllAssets
For Each varItem In ctl.ItemsSelected
rs1.AddNew
rs1!AssetNo = ctl.ItemData(varItem)
rs1!DateOfService = Me.DateOfService
rs1!NextServiceDate = Me.NextServiceDate
rs1!ServiceProvider = Me.ServiceProvider
rs1!ServiceType = Me.ServiceType
rs1!ServiceRef = Me.ServiceRef
rs1!Details = Me.Details
rs1.Update
rs2.FindFirst "id =" & ctl.ItemData(varItem)
rs2.Edit
rs2!NextServiceDate = Me.NextServiceDate
rs2.Update
Next varItem
rs1.Close
rs2.Close

 

[vbaInet]

You're forgiven

Anyway, what is it you're doing with your code?

 

[cocowomble]

I need to put in 3 different variations of this loop, and depending on what the user selects from a list box will depends on which loop is ran.

what I was thinking, is if I could put each of the loop into a variable, I could then run a if statement.

does that make more sense?

 

[vbaInet]

Put the loop to one side first and tell us what the objective is.

 

[cocowomble]

I have two table. Table 1 (assets) list all my assets etc. Table 2 lists all servicing for each asset.

I have a form which generates a list box of items that require servicing (once servicing has been completed on these items) it update all records, in both tables. Table 2 with all the information about the service. and Table 1 with (only) the "next service date".

I have set the list box to allow multiple selection and have used the following code to allow new records to be update in table 2 (which works).

This works fine.

I have added a list box which allows three options, Routine Service (which the aboves works fine), repair (which I need the "next service date" not to be update in table 1) and inspection (which I need the "next service date" not to be update in table 1).

I really hope that makes sense

 

[vbaInet]

It really isn't making much sense to me. How can you use only a listbox to update tables? It's just not logically possible.

Can you upload a test sample of your database so I can see how it's set up?

 

[jdraw]

Can you tell us in plain, simple English -- no Access/database jargon --WHAT you are trying to do? Once readers understand WHAT, they may give responses or options that are focused on the issue.

 

[JHB]

..
I have added a list box which allows three options, Routine Service (which the aboves works fine), repair (which I need the "next service date" not to be update in table 1) and inspection (which I need the "next service date" not to be update in table 1).

Something like below.

  Set ctl = Me.listSeeAllAssets
  For Each varItem In ctl.ItemsSelected
    If Me.YourListBoxName = "Routine Service" Then
      rs1.AddNew
      rs1!AssetNo = ctl.ItemData(varItem)
      rs1!DateOfService = Me.DateOfService
      rs1!NextServiceDate = Me.NextServiceDate
      rs1!ServiceProvider = Me.ServiceProvider
      rs1!ServiceType = Me.ServiceType
      rs1!ServiceRef = Me.ServiceRef
      rs1!Details = Me.Details
      rs1.Update
      rs2.FindFirst "id =" & ctl.ItemData(varItem)
      rs2.Edit
      rs2!NextServiceDate = Me.NextServiceDate
      rs2.Update
    ElseIf Me.YourListBoxName = "Repair" Then
      'Do some code here
    ElseIf Me.YourListBoxName = "Inspection" Then
      'Do some code here
    End If
  Next varItem
  rs1.Close
  rs2.Close