GoToRecord and subforms

[chanson]

I'm experiencing a problem that would make me pull my hair out if I had any. I have a form that displays records (FOB's) in a subform datasheet. I have code setup so when I push "ctrl-d" it runs some delete code which in turn opens another form that forces you to select a different FOB to assign to any detail records that have the FOB that is being deleted. After I delete said FOB the form has to refresh of course and then move back to the same row as the previously deleted record. This is the code I'm trying to use to do this functionality.

    Dim int_Row As Integer
    Dim I As Integer
    int_Row = Forms!frm_FOB!chd_FOB.Form.CurrentRecord
    
    DoCmd.SetWarnings False

    DoCmd.RunSQL "Update tbl_Manufacturer set FOB_ID = " & Me.cbo_ID.Value & " where FOB_ID = " & [Forms]![frm_FOB]![chd_FOB].[Form]![txt_ID]
    DoCmd.RunSQL "Delete from tbl_FOB where ID = " & [Forms]![frm_FOB]![chd_FOB].[Form]![txt_ID]
    Forms!frm_FOB!chd_FOB.Form.Requery
    Forms!frm_FOB!chd_FOB.SetFocus
    Forms!frm_FOB!chd_FOB.Form.FOB.SetFocus
    For I = 0 To (int_Row - 2)
        DoCmd.GoToRecord acActiveDataObject, , acNext
    Next

Now, when I run this code and it hits the GoToRecord line it comes back with the error "You can't go to the specified record". Any thoughts? BTW, the FOB in this line "Forms!frm_FOB!chd_FOB.Form.FOB.SetFocus" is actually a field name.

 

[chanson]

No ideas?

 

[Surjer]

just a guess not 100% on this

DoCmd.GoToRecord acActiveDataObject, , acNext

have you tried taking "acActiveDataObject" out of it

DoCmd.GoToRecord , , acNext

 

[smercer]

When the error comes up is the selected record in the new record?

If so try inserting this code just before the goto record line

If Me.NewRecord Then 
Exit sub
end if

Does this help?

 

[chanson]

DoCmd.GoToRecord acActiveDataObject, , acNext

have you tried taking "acActiveDataObject" out of it

DoCmd.GoToRecord , , acNext

Tried that with no success.

When the error comes up is the selected record in the new record?

If so try inserting this code just before the goto record line

If Me.NewRecord Then 
Exit sub
end if

Does this help?

Nope, it would be in the middle of several records, like this:

Row1
Row2
Row3
Row4
Row5
Row6

Where I wanted to delete Row4 and then it would move the cursor to Row 5 like this:

Row1
Row2
Row3
Row5
Row6

 

[smercer]

I have done a simalar thing in want you are trying to achive.

try this:

Dim Record_Count as integer
Dim Record_selected as integer

Record_Count = DCount("Primary_Key_Field", "tbl_your_record_source", "Your criteria")'this will count the amount of records to be processed

If Record_Count = 0 Then GoTo End_me 'if there are no records

Record_selected = 1 'this is for going through all records in your table to process each individualy

Me!YourSubform.SetFocus 'this is to set focus to a subform to go to first record and finalise each individualy

DoCmd.GoToRecord , , acFirst 'go to first record

Do While Record_Count <> Record_selected 'this will go through all records in your Subform to Process each individualy

Record_selected = Record_selected + 1 'this will keep track of which record is selected
    
' your code you want to repeat for each record goes here
    
    DoCmd.GoToRecord , , acNext 'this will go to the next record and repeat the do while procedure
Loop 'repeat
If Record_Count = Record_selected Then 'this is for when the last record is reached the do while will not do this
 
'Repeat your code here so it is the same as in the loop
End If

Does this help?

 

[chanson]

Well, that code uses the same GoToRecord command as my code and that is the line I'm having trouble with. I think the issue is because of the fact that I'm trying to run this code from Form A to access the subform on Form B. When I have this code on Form B it works great with the subform in Form B. I think I will have to figure out how to put this code on Form B. Thanks for the help tho bro, I sure appreciate it!

 

[smercer]

You have not even tried it and you are stating it wont work from looking at the one line. I got it from my data base and it is working code.

I think that what the code has done before the line may be the problem or that the problem may be that your code leaves a required field empty before the line that creates the error.

 

[Surjer]

Docmd does not work for a remote form I dont think...

I would put the code back into formA in a Public Sub and then call it from formB like

On FormA

Public Sub MyFunction()
'Do your code here that does what you want
End Sub

On FormB
On a Push button or whatever

[Forms]![formA].MyFunction