VBA help with Access Form

ptw78 · Mar 25, 2010

I have a form in access. Below is the original code I had in the form. It worked fine, but I have to change it now. I made what I thought where the correct changes but I'm getting an error. I've tried two different ways and am getting two different errors.

Here is the original code that worked. S_Code_Type is for a dropdown list in a form.

Code:

Private Sub Form_Load()

Dim Sessions As Object, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession

Do

    Sess0.Screen.MoveTo 3, 18
    Sess0.Screen.SendKeys (Account_Number & "<EraseEOF><Enter>")
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    Sess0.Screen.MoveTo 5, 73
    Sess0.Screen.SendKeys (S_Code_Type & "<Enter>")
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    DoCmd.RunCommand acCmdRecordsGoToNext
    
Loop Until IsNull(Account_Number)
    
DoCmd.Close
    
End Sub

Here is my first effort at a change that didn't work.
I received compiler error Else without IF

Code:

Private Sub Form_Load()

Dim Sessions As Object, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession

Do

    Sess0.Screen.MoveTo 3, 18
    Sess0.Screen.SendKeys (Account_Number & "<EraseEOF><Enter>")
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    Sess0.Screen.MoveTo 5, 73
    'Sess0.Screen.SendKeys (S_Code_Type & "<Enter>")
    If S_Code_Type = S77 Then Sess0.Screen.Senkeys ("F77" & "<Enter>")
'**complier error received here on next line ElseIf S_Code_Type = S78
    ElseIf S_Code_Type = S78 Then Sess0.Screen.Senkeys ("F78" & "<Enter>")  
    ElseIf S_Code_Type = S79 Then Sess0.Screen.Senkeys ("F79" & "<Enter>")
    ElseIf S_Code_Type = S80 Then Sess0.Screen.Senkeys ("F80" & "<Enter>")
    End If
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    DoCmd.RunCommand acCmdRecordsGoToNext
    
Loop Until IsNull(Account_Number)
    
DoCmd.Close
    
End Sub

my third attempt w/a select case. Error I received here was runtime error 2046
command or action 'RecordsGoToNext' isnt available now.

Code:

Private Sub Form_Load()

Dim Sessions As Object, System As Object, Sess0 As Object
Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession

Do

    Sess0.Screen.MoveTo 3, 18
    Sess0.Screen.SendKeys (Account_Number & "<EraseEOF><Enter>")
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    Sess0.Screen.MoveTo 5, 73
    Select Case S_Code_Type
        Case Is = S77:
            Sess0.Screen.Senkeys ("F77" & "<Enter>")
        Case Is = S78:
            Sess0.Screen.Senkeys ("F78" & "<Enter>")
        Case Is = S79:
            Sess0.Screen.Senkeys ("F79" & "<Enter>")
        Case Is = S80:
            Sess0.Screen.Senkeys ("F80" & "<Enter>")
    End Select
        
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    DoCmd.RunCommand acCmdRecordsGoToNext  '****runtime error here
    
Loop Until IsNull(Account_Number)
    
DoCmd.Close
    
End Sub

dhookom · Mar 25, 2010

I don't know if the : is actually expected in the s_code_Type value or if you thought you needed to add it. Try:

Code:

    Select Me.Case S_Code_Type
        Case "S77"
            Sess0.Screen.Senkeys ("F77" & "<Enter>")
        Case "S78"
            Sess0.Screen.Senkeys ("F78" & "<Enter>")
        Case "S79"
            Sess0.Screen.Senkeys ("F79" & "<Enter>")
        Case "S80"
            Sess0.Screen.Senkeys ("F80" & "<Enter>")
    End Select

Duane
Hook'D on Access
MS Access MVP

ptw78 · Mar 25, 2010

I put the 'is' in because i thought i needed it. I tried your way and got Compiler Error: Expected Case

dhookom · Mar 25, 2010

Oops. You need to correct my mistakes. I attempted to make the reference to the combo box more explicit by adding [red]Me.[/red] but I entered it into the wrong place.

Code:

    Select Case [b][red]Me.[/red][/b]S_Code_Type
        Case "S77"
            Sess0.Screen.Senkeys ("F77" & "<Enter>")
        Case "S78"
            Sess0.Screen.Senkeys ("F78" & "<Enter>")
        Case "S79"
            Sess0.Screen.Senkeys ("F79" & "<Enter>")
        Case "S80"
            Sess0.Screen.Senkeys ("F80" & "<Enter>")
    End Select

Duane
Hook'D on Access
MS Access MVP

ptw78 · Mar 25, 2010

This is Access 2000 also if that matters at all.

ptw78 · Mar 25, 2010

K, I tried it, still received the same error:
runtime error 2046
command or action 'RecordsGoToNext' isnt available now.

ptw78 · Mar 25, 2010

anyone able to help on this? after searching the net it seems this is a common error but I can't seem to find a solution, i've tried using Me.Dirty and that didn't work, although I could be coding it work.

dhookom · Mar 25, 2010

I'm not sure why this code is run automatically on the Load event. If you want to act on all records from the form's record source, you might consider creating a recordset using the form's recordsetclone. You could then movenext through the recordset rather than

Code:

DoCmd.RunCommand acCmdRecordsGoToNext

Duane
Hook'D on Access
MS Access MVP

ptw78 · Mar 25, 2010

You have a sample or a site where I can take a look at? I'm not too familiar with access/recordset.

dhookom · Mar 25, 2010

Consider searching this site (or others) for RecordSetClone. If you don't find something, come on back.

It might also help if you explained what you are attempting to do with your code.

Duane
Hook'D on Access
MS Access MVP

ptw78 · Mar 25, 2010

I'll take a look. But basically what this does; A query is made/ran from data that is put into another form(the main form). The query is ran and the form(this sub form) gets it's data from that query. Then the vba is coded to take that data in this sub form to update Attachmate Extra Mainframe. I actually don't even need this sub form to do it, but it was the only way I could figure out how run it all. I'm guessing you could run it from the table the query makes but I don't know how to do that. But we had some changes and those are above and now I can't get the vba macro to run w/o the above errors.

dhookom · Mar 25, 2010

If you want to do some process of all the records in a query, you might want to just open a recordset like:

Code:

  Dim db As DAO.Database
  Dim rs as DAO.Recordset
  Dim strSQL as String
  'create the SQL statement similar to your former form record source
  strSQL = "SELECT S_Code_Type, ... FROM ... WHERE ..."
  set db = Currentdb
  Set rs = db.OpenRecordset(strSQL)
  With rs
    .MoveFirst
    Do Until .eof
       'processing code goes here
        Select Case !S_Code_Type
           Case...
           Case...
        End Select
       .MoveNext
    Loop
    .Close
  End With
  set rs = nothing
  set db = nothing

Duane
Hook'D on Access
MS Access MVP

ptw78 · Mar 25, 2010

This is what I came up with. The SQL statement I used the statment from the original query. Will that work? And I'm assuming that I just put this in on the button click on the main form? Thanks

Code:

  Dim db As DAO.Database
  Dim rs as DAO.Recordset
  Dim strSQL as String
  'create the SQL statement similar to your former form record source
  strSQL = "SELECT FHAReferrals.Date, FHAReferrals.Account_Number, FHAReferrals.[1st 		Review Processor], FHAReferrals.[2nd Review Processor], 		FHAReferrals.[S Code], FHAReferrals.S_Code_Type, 		FHAReferrals.Decision
	FROM FHAReferrals
	WHERE (((FHAReferrals.Date) Between Date() And Date()-2) AND 		((FHAReferrals.[S Code])=True) AND 		((FHAReferrals.Decision)="Referred"));"
  set db = Currentdb
  Set rs = db.OpenRecordset(strSQL)
  With rs
    .MoveFirst
    Do Until .eof
           Sess0.Screen.MoveTo 3, 18
    	   Sess0.Screen.SendKeys (Account_Number & "<EraseEOF><Enter>")
    	   Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
    	   Sess0.Screen.MoveTo 5, 73
        Select Case !S_Code_Type

        	Case "S77"
            		Sess0.Screen.Senkeys ("F77" & "<Enter>")
        	Case "S78"
            		Sess0.Screen.Senkeys ("F78" & "<Enter>")
        	Case "S79"
            		Sess0.Screen.Senkeys ("F79" & "<Enter>")
        	Case "S80"
            		Sess0.Screen.Senkeys ("F80" & "<Enter>")
        End Select
       .MoveNext
    Loop
    .Close
  End With
  set rs = nothing
  set db = nothing

ptw78 · Mar 25, 2010

I'm getting a compiler error: expected: ) with Between highlighted in the below code. I'm not sure why, it's the exact statement in the query I made that works. Except it's all one line and not split like below. thx

Code:

strSQL = "SELECT FHAReferrals.Date, FHAReferrals.Account_Number, FHAReferrals.[1st Review Processor], FHAReferrals.[2nd Review Processor], FHAReferrals.[S Code], FHAReferrals.S_Code_Type, FHAReferrals.Decision"
FROM FHAReferrals
WHERE (((FHAReferrals.Date) Between Date() And Date()-2) AND ((FHAReferrals.[S Code])=True) AND ((FHAReferrals.Decision)="Referred"));"

dhookom · Mar 25, 2010

I would change the strSQL to:

Code:

  strSQL = "SELECT Account_Number, S_Code_Type " & _
    "FROM FHAReferrals " & _
    "WHERE [Date] Between Date() And Date()-2) AND " & _
    "[S Code]=True AND Decision='Referred'"

Also, it looks like Account_Number comes from the recordset so you must prefix it with the bang "!" like W_Code_Type.

Code:

           Sess0.Screen.SendKeys (!Account_Number & "<EraseEOF><Enter>")

Duane
Hook'D on Access
MS Access MVP

ptw78 · Mar 25, 2010

K, here is the error I get, and this is one I've never seen before probably due to not knowing much about DAO
Compiler Error:
User-Defined type not define

Code:

 Dim db As DAO.Database

dhookom · Mar 25, 2010

You need to set a reference to the MS DAO object library. While in the module, select Tools->References and find the highest version number of this library and check it. Then try to compile your code to see if you checked the right one.

Duane
Hook'D on Access
MS Access MVP

ptw78 · Mar 26, 2010

Gotten a little further, still have some errors. First I received a 3075 error. So the removed the 2nd parenthesis out of Date()-2).

Code:

"WHERE [Date] Between Date() And Date()-2) AND " & _

That seemed to work. Now I have a runtime error 3021: No Current Recored at

Code:

.MoveFirst

MajP · Mar 26, 2010

the better construct is

Code:

'normally this is not even needed, because instantiated 
'recordsets normally are at the first record
'but to be safe
if not rs.eof and rs.bof then
  rs.moveFirst
end if
'Most of the time this always works by itself, but no harm
'using above
do while not rs.eof
  ....
  rs.movenext
loop

your error is because you are moving to the first records, but have returned 0 records. The above construct will never give this error.

Golom · Mar 26, 2010

No current record" means that you are at end-of-file (EOF) or beginning-of-file (BOF) or the recordset is empty (both EOF and BOF).

Try

Code:

With rs
    If NOT (.EOF and .BOF) Then
       .MoveFirst
       Do Until .EOF
           :
          .MoveNext
       Loop
    Else
       MsgBox "Recordset is Empty"
    End If
End With

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

VBA help with Access Form

Technical User

Programmer

Technical User

Programmer

Technical User

Technical User

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Technical User

Programmer

Technical User

Programmer

Technical User

Technical User

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor