Next record error

[musclecarlover07]

I have the following code.

    Dim ctl As Control
    
    For Each ctl In Me
        Me.Assigned = Me.Combo55
        DoCmd.RefreshRecord
        DoCmd.GotoRecord , , acNext
    Next ctl

I keep getting an error that says cannot go to the specified record. How can I go about fixing this?

 

[bob fitz]

Where are you using the code.
Which line throws the error.
Why are you stepping through all the controls if you just want to assign the value of Combo55 to the control called Assigned.

 

[musclecarlover07]

After I think about it I don't think the For each method would work. I have only ever used it when I have had similar named fields, like Assigned1, Assigned2 and so on. Thats not the case in this situation.

I then tried this bit of code

Do Until Me.CurrentRecord = acLast
    Me.Assigned = Me.Combo55
    DoCmd.GoToRecord , , acNext
Loop

But it doesn't do the last record. If I can get this do the last record it will be solved.

 

[bob fitz]

Can you explain in plain English what you are trying to do.

 

[musclecarlover07]

I have a form with a multiple select list box. I select any number of records click a button it then opens a Continuous form with all the selected records.

In the Continuous form I want to select and option in a combo box then hit a button and all the records that were selected will change a specific field to the value of the combo box.

 

[bob fitz]

Might be better to use a recordset or an Update query.

 

[musclecarlover07]

Im sorry but I have no idea how to do that or even where to begin.

 

[bob fitz]

For some info on recordsets take a look at: http://www.utteraccess.com/wiki/index.php/Recordsets_for_Beginners
Google "access update query" for more on Update queries

 

[musclecarlover07]

Ok that helped understand so much. But I don't know if im missing something with the looping through the record set. This is what I have so far:

    Dim rs As DAO.Recordset
    Dim db As Database
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("MainWorkNew")
    
    If rs.RecordCount <> 0 Then
        rs.MoveFirst
        While Not rs.EOF
            Me.Assigned = Me.Combo55
            rs.MoveNext
        Wend
    End If
    
    rs.Close
    Set rs = Nothing

Its not looping through.

 

[jdraw]

Your code is only assigning a value to one control.

Me.Assigned = Me.Combo55

and I join Bob in asking you, What are you trying to do in plain English?

Your code is working with a combo, but you said

multiple select list box.

I'm confused.

Perhaps you are trying to loop through the list box SelectedItems ???

 

[musclecarlover07]

So I have attached a copy of my test database.

1. First, open "Form4"
2. Select 2+ records
3. Click Command 2.
Now in Form3
4. LastName field select and option
5. Click Command54

On the command54 I want all of the records to change Field Assigned to what was in Combo55 (LastName)

 

[jdraw]

Don't use lookups at the table field level. Details here.

Following

Now in Form3
4. LastName field select and option

when I click Lastname, all I see are numbers????

I modified your code, as follows, to see what was being assigned.

Private Sub MassSelectMe_Click()
    

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("MainWorkNew")
If rs.RecordCount <> 0 Then
 rs.MoveFirst
 While Not rs.EOF

   Me.Assigned = Me.Combo55
Debug.Print Me.Combo55 '<----------------added to see what is being assigned
   rs.MoveNext
 Wend
End If
rs.Close
Set rs = Nothing

End Sub

Debug.print output is

 2 
 2 
 2 
 2 
 2

So you code is looping. It's just that it is assigning the same value to the same field on each iteration of the loop.

I think you should tell readers, at least Bob and me, WHAT you are trying to do in plain English, since your code hasn't changed and my test has confirmed what I said in post #10.

 

[musclecarlover07]

What do you mean don't use look ups at the table level? Yes those are suppose to be numbers. Its the ID number of the name for the person to assigned it. That was the only way I could get it to work. I had another form but deleted it on accident .
Basically the names had ID so when I open Form2 (Deleted) whatever the name was it put that value as the tabs caption.

For ID number was linked with Page1 and so on. So if member in the first spot left then I could change his name to another name or leave it blank. I couldnt get it to show the Actual name for the value. Im not to worried about that. I just need the main issue fixed.

 

[jdraw]

So in the Lastname select combo, you display a series of numbers. Are you the only user? How would anyone else possibly know that 3 or 7 was a LastName?

I would like you to respond to this:

I think you should tell readers, at least Bob and me, WHAT you are trying to do in plain English, since your code hasn't changed and my test has confirmed what I said in post #10.

 

[musclecarlover07]

I actually didnt see the updated code you had. But I just need it to go through all the records an change the value.

 

[jdraw]

I just need it to go through all the records an change the value.

What would you say to someone who gave you this as a statement of work?
Can you flesh this out just little?

 

[musclecarlover07]

Again ALL the records that are on Form3 NEED TO BE CHANGED. The records that are on form3 are selected from Form4.

On Form4 you select the records that need to be modified. Once you click the button it then opens Form3. Only the selected records are shown. Then I want to select a value from the combo box. Then you hit the button that is on Form3 And all the records that were selected and that are current on Form 3 are changed.

In the database follow these instructions to have a visual representation. I don't need anything else to be modified or changed.

1. First, open "Form4"
2. Select 2+ records
3. Click Command 2.
Now in Form3
4. LastName field select and option
5. Click Command54

If you selected the first three records those are the only records I need to be changed. So when I selected an option within the combo box and hit the button only those records are modified.