Looping problem 1

[swk003]

Hi I'm a VB newbie and was wondering if someone might be able to help me with a looping problem. I am trying to adapt an if/ else statement to include a counting loop which if condition not met I want programme to update an errorlog table with txtbox data. here's the spec: -

'User inputs PID, presses TAB and afterUpdate()searches table tbl_Abbot_PID for PID.
'if correct PID message box displays PID, curser moves to eye txtbox.
'if incorrect PID message displays incorrect PID and moves curser back to previous txtbox. I wish to add an expression or loop to limit the number of attempts the user has to insert correct ID to twice. If PID incorrect the second time around I then need to insert txtbox PID data into tbl_errorlog and move focus to eyetext box and keep going

Private Sub Photoset_PID_AfterUpdate()

Dim CkSt As Integer
Dim strmsg As Variant
Dim RGCarrivalDate As Date
Dim cn As ADODB.Connection
Dim rs As DAO.Recordset ' requires DAO reference

'assumes number data type for Abbot_PID
Set rs = CurrentDb.OpenRecordset( _
"Select * From tbl_Abbot_PID " & _
"Where Abbot_PID =" & Me.[Photoset_PID])

If rs.EOF And rs.BOF Then
'I think i need to add a counting loop here???
'For k = 0 To 1
CkSt = 0
strmsg = "incorrect PID"
' move focus back to previous text box
Me.[txtDateVisible].SetFocus
'Next
Else
CkSt = 1
strmsg = rs.Fields(0)
End If

MsgBox strmsg
If CkSt = 1 Then
Me.[txtEye].SetFocus
RGCarrivalDate = rs.Fields(1)
Me.txt_RGC_arrival_date = rs.Fields(1)
Else
Me.[txtDateVisible].SetFocus
Dim cn As ADODB.Connection
cn.Open "provider= microsoft.jet.oledb.4.0;" & "data source=" & "D:\Swk\Photoset PTB Project\Photoset_PTB_1.1.mdb
cn.Execute "INSERT INTO tbl_Errorlog " & "(Abbot_PID) VALUES (" & Me.txt_testbox & ");"
Me.[txtEye].SetFocus 'reset

End If
rs.Close
cn.Close
Set rs = Nothing

End Sub


thanks.

 

[Larsson]

You can't use a loop because the after_Update event fires every time the user change the value.

Instead you use a global variable to hold how many times the user has input the wrong value.

Some simple code to explain:

Private numberOfWrongInput As Integer

Private Sub Photoset_PID_AfterUpdate()
   If (numberOfWrongInput > 2) Then
       'The code when the user has type wrong input 
       'to many times.
   ElseIf (Me.txtInput <> rightValue) Then
       'Increase the numberOfWrongInput with 1.
       numberOfWrongInput = numberOfWrongInput + 1
   Else
       'The code for right input.
       numberOfWrongInput = 0   
       'Don't forget to reset the counter here.
   End If
End Sub

Markus

 

[TheAceMan1]

How are ya swk003 . . . . .

In the [purple]BeforeUpdate[/purple] event of [blue]Photoset_PID[/blue] try this:

[blue]   Dim FoundID, Criteria As String, cn As ADODB.Connection
   Dim Msg As String, Style As Integer, Title As String
   [purple][b]Static[/b][/purple] subCalls As Integer
   
   Criteria = "[Abbot_PID] =" & Me!Photoset_PID
   FoundID = DLookup("[Abbot_PID]", "tbl_Abbot_PID", Criteria)
   
   If IsNull(FoundID) Then
      [purple][b]subCalls[/b][/purple] = [purple][b]subCalls[/b][/purple] + 1
      Msg = "Incorrect PID '" & Me!Photoset_PID & "'"
      Style = vbInformation + vbOKOnly
      Title = "PID '" & Me!Photoset_PID & "' Not Found! . . ."
      MsgBox Msg, Style, Title
      
      If [purple][b]subCalls[/b][/purple] >= 2 Then
         cn.Open "provider= microsoft.jet.oledb.4.0;" & "data source=" & _
                 "D:\Swk\Photoset PTB Project\Photoset_PTB_1.1.mdb"
         cn.Execute "INSERT INTO tbl_Errorlog " & _
                    "(Abbot_PID) VALUES (" & Me.txt_testbox & ");"
        [purple][b]subCalls[/b][/purple] = 0
      Else
         Cancel = True
      End If
   Else
      [purple][b]subCalls[/b][/purple] = 0
   End If[/blue]

See Ya! . . . . . .

 

[swk003]

Hello AceMan1 and Markus

Many thanks for your support. You have both shown me the road ahead. Still a little lost here though!

AceMAn1, with your code are you suggesting I run the intended procedure from the the beforeUpdate() event and not the aftreUpdate() event, or is it that I need to place the global variable in a different procedure; Or can I stick with static variable and keep this all in the same procedure.

I decided to try the code as it looks technically alot better than my shambled coding. If I insert a correct PID, the cursor moves to the next txtbox (which i want). However if inserting an incorrect PID, the messagebox displays incorrect PID (and the number)however the cursor stays in the Photoset_PID txtbox, if you then retype the incorrect PID, i get a runtim error '91', object variable or with block variable not set. debug takes me to the following line:

cn.Open "provider= microsoft.jet.oledb.4.0;" & "data source=" & _
"D:\Swk\Photoset PTB Project\Photoset_PTB_1.1.mdb"

Can you assist?

thanks

swk003

 

[swk003]

Sorry I failed to create databse connection object:-

Set cn = New ADODB.Connection

Will come back with latest findings.

swk003

 

[Larsson]

Just a small suggestion.
When you want to execute a SQL in the same database as the one you have open (like the code TheAceMan1 suggest), then you can use CurrentDb.Execute instead.

For exampel, TheAceMan1's code:

cn.Open "provider= microsoft.jet.oledb.4.0;" & "data source=" & _
            "D:\Swk\Photoset PTB Project\Photoset_PTB_1.1.mdb"
cn.Execute "INSERT INTO tbl_Errorlog " & _
             "(Abbot_PID) VALUES (" & Me.txt_testbox & ");"

This you can write like:

CurrentDb.Execute "INSERT INTO tbl_Errorlog " & _
             "(Abbot_PID) VALUES (" & Me.txt_testbox &

CurrentDB allways use the current connection.


Markus

 

[TheAceMan1]

[blue]are you suggesting I run the intended procedure from the the [purple]beforeUpdate()[/purple] event and not the [purple]aftreUpdate()[/purple] event . . . .[/blue]

Yes! The [blue]BeforeUpdate[/blue] event is one of the few that uses the [blue]Cancel arguement[/blue]. When set to to [blue]true[/blue] (in this case), [purple]updating of the field/control is cancelled and focus remains at the control (PID)[/purple]. This is its main use and what keeps you at PID if the search fails (twice allowed).

To correct the error which occurs after retyping, add the following line in [purple]purple[/purple] where you see it:

[blue]   Cancel = True
   [purple][b]Me!PID.Undo[/b][/purple][/blue]

[blue]Or can I stick with static variable and keep this all in the same procedure. [/blue]

Yes . . . [purple]the static variables stays in memory[/purple] (don't have to worry about its value falling out of scope), and it should all be in the same routine.

[blue]however the cursor stays in the Photoset_PID txtbox . . .[/blue]

This is what you requested if the search failed!

[blue]Add the [purple]Undo[/purple] an let me know the results . . . .[/blue]

See Ya! . . . . . .

 

[swk003]

Hi AceMan1

You are a genius. This has cleared the txtbox and removes the need to setfocus() to previous txtbox. Once again thank you for all your help.

swk003

 

[swk003]

Hi again AceMan1

I was a little hasty I think. The Photoset_PID txtbox is cleared whilst subcalls static variable is < 2 however once >= 2 and the errorlog table has been updated, Photoset_PID retains the error value and cursor then moves to the next txtbox on the form?? Have tried putting the

Cancel = True
Me!Photoset_PID.Undo

argument and undo at various stages in the code but no luck!! can you explain how I get out of this mess??

thanks

swk003

 

[TheAceMan1]

[blue]I wish to add an expression or loop to limit the number of attempts the user has to insert correct ID to twice. [purple]If PID incorrect the second time around I then need to insert txtbox PID data into tbl_errorlog and move focus to eyetext box and keep going[/purple][/blue]

You didn't specify what to do with the value of PID textbox when 2nd attempt fails. Try adding following line in purple where you see it:

[blue]         cn.Execute "INSERT INTO tbl_Errorlog " & _
                    "(Abbot_PID) VALUES (" & Me.txt_testbox & ");"
[purple][b]         Me!PID=""[/b][/purple][/blue]

See Ya! . . . . . .

 

[swk003]

AceMan1

I am getting a runtime error 2115 - macro or function set to beforeUpdate() or validation rule property preventing access saving data in field. the debug takes me straight to the following line:-

Me!Photoset_PID = ""

I have added no validation rules either

here's the full procedure:

Private Sub Photoset_PID_BeforeUpdate(Cancel As Integer)

Dim FoundID, Criteria As String
Dim cn As ADODB.Connection
Dim Msg As String, Style As Integer, Title As String
Dim Msg1 As String, Style1 As Integer, Title1 As String
'static or a global variable as subCalls must not be reset to 0 after every attempt to loop the Photoset_PID input
Static subCalls As Integer

Criteria = "[Abbot_PID] =" & Me!Photoset_PID
FoundID = DLookup("[Abbot_PID]", "tbl_Abbot_PID", Criteria)

If IsNull(FoundID) Then
subCalls = subCalls + 1
Msg = "Incorrect PID '" & Me!Photoset_PID & "'"
Style = vbInformation + vbOKOnly
Title = "PID '" & Me!Photoset_PID & "' Not Found! . . ."
MsgBox Msg, Style, Title


If subCalls >= 2 Then
Msg1 = "Incorrect PID '" & Me!Photoset_PID & "'"
Style1 = vbInformation + vbOKOnly
Title1 = "PID '" & Me!Photoset_PID & "' added to Errorlog"
MsgBox Msg1, Style1, Title1

Set cn = New ADODB.Connection
cn.Open "provider= microsoft.jet.oledb.4.0;" & "data source=" & "D:\Swk\Photoset PTB Project\Photoset_PTB_1.2.mdb"
cn.Execute "INSERT INTO tbl_Errorlog " & "(Abbot_PID) VALUES (" & Me.Photoset_PID & ");"
Me!Photoset_PID = ""
' or can use CurrentDb.Execute "INSERT INTO tbl_Errorlog " &_
' "(Abbot_PID) VALUES (" & Me.Photoset_PID & ");"
'cn.Close
'Set cn = Nothing
subCalls = 0
Else
' The BeforeUpdate event is one of the few that uses the Cancel argument. When set to true (in this case), updating of the field/control is cancelled and focus remains at the control (Photoset_PID). This is its main use and what keeps you at Photoset_PID if the search fails (twice allowed).
Cancel = True
'instead of setting focus (Me.txtDateVisible.SetFocus) we use below argument
Me!Photoset_PID.Undo
End If
Else
subCalls = 0


End If

End Sub

 

[TheAceMan1]

swk003 . . . . .

Sorry for getting back so late. I know your anxious . . .

Instead of:
[tt][purple]Me!Photoset_PID = ""[/purple][/tt]
Try:
[tt][purple]Me!Photoset_PID.Undo[/purple][/tt]

If you wish the focus to stay @ Photoset_PID, try:
[tt][blue] Cancel = True
Me!Photoset_PID.Undo[/blue][/tt]

Let me know . . . .

See Ya! . . . . . .

 

[swk003]

AceMan1

It works. Many thanks. I am sure I tried this before! In my high state of anxiety maybe i overllokked some minor detail.

cheers

swk003