Visual Basic problem (1 Viewer)

[davegillian]

I have a form that filters certain records out of a table "Customer Details" in my database via the Y/N field Checked where the value is No.

I want a command button on the form that will run a macro that will update the Checked value to Yes and then move to the next record (displaying it in the same form) until the last form is displayed at which point I would like the value to be updated and the form to be closed.

Using the GoToRecord - Next action in my Macro I can achieve the first part but when the last record is reached an error message is displayed.

I think I can get around this by using the EOF command in Visual Basic but I am new to this language and the code I've written is clearly not correct!

Could anyone suggest a correction to my code or another method for this?

Cheers,

Dave

Function Mark_As_Checked___Move_To_Next_Record()
On Error GoTo Mark_As_Checked___Move_To_Next_Record_Err

DoCmd.OpenQuery "Mark As Checked", acViewNormal, acEdit

Dim stDocName As String
Dim Unchecked As Recordset

Set Unchecked = [new training db].OpenRecordset(SelSQl = "SELECT [Customer Details].* FROM [Customer Details] WHERE ((([Customer Details].Checked)=No));", dbOpenDynaset)

stDocName = "Mark As Checked"

Do Until Unchecked.EOF
DoCmd.OpenQuery stDocName
DoCmd.GoToRecord , "", acNext
Loop

stDocName = "Customer Details Complete"

DoCmd.Close stDocName

Mark_As_Checked___Move_To_Next_Record_Exit:
Exit Function

Mark_As_Checked___Move_To_Next_Record_Err:
MsgBox Error$
Resume Mark_As_Checked___Move_To_Next_Record_Exit

End Function

 

[Rich]

Just use an UpdateQuery

 

[davegillian]

I have an update query to update my field but how will that help with the do until loop? The point of this bit of code I'm stuck on would be to DO run update query and move to next record UNTIL record=last (EOF?) THEN run update query and close object (form).

Cheers,

Dave

Just use an UpdateQuery

 

[Rich]

You don't need to loop, just set the criteria of the update to that of those records displayed on the form

 

[davegillian]

The update query is a side point really. I want to disable navigation buttons and the close button so that the only way out of the form is to click the "move to next record" command button.

I only want the field to be updated to "checked" if the form has been viewed (checked) and the command button pressed to move on. The only way I know how to get the command button to move to the next record is to perform that action is as part of a macro (which coincidentally will also contain the update query, which would update the current record only).

If I use this method though when I get to the final filtered record clicking on the results in an error message, which I'm trying to avoid by using the loop, which will flip from "move to next record" to "close" once the last record is reached.

Sorry to be a pain in the bum!

Dave

You don't need to loop, just set the criteria of the update to that of those records displayed on the form

 

[davegillian]

Hi if anyone sees this post and identifies with my problem then I've found a solution so I thought I'd share the love!

In the Macro design view use the GoToRecord Next command. Once the last record is reached a blank data entry record will be displayed. The next line in the Macro should be Close Form with the condition Me.[NewRecord] or [Forms]![Form Name].[NewRecord] if the Macro is activated from another form.

In Visual Basic:

DoCmd.GoToRecord acForm, "Form Name", acNext
If (Forms![Form Name].NewRecord) Then
DoCmd.Close acForm, "Form Name"
End If



PS. Does anybody know of a website that lists all these commands...I stumbled across the .NewRecord and .Recordcount commands whilst looking for this problem. If I'd had a list of commands to browse through I'd have solved the problem days ago!