Event procedure not working

G

Graeme

I've created a an event procedure to run when a button in a form is clicked.

It basically calls a sub / function from a standard module.

The problem I have is that i can't get the code to run when the button is
pressed.

I've run the code from within VBE and it works fine.

Can anyone help?

Event Procedure:

Private Sub LoadDetailTB_Click()
Call ReadDetailTB
End Sub

It calls the following Sub / Function (I've tried it as both and it runs in
both instances):

Function ReadDetailTB()
' import a large txt file into Access ready and convert to old skool report
format
' to keep it simple the table will be predefined as DetailTB

Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim retval, SQL As String
Dim sTime As Date

DoCmd.Echo False
DoCmd.SetWarnings False

DoCmd.RunSQL ("Delete * from DetailTB")

sTime = Now()
Set db = CurrentDb
Set rs = db.OpenRecordset("DetailTB", dbOpenDynaset)

Dim IFile As String
Dim iRow As Long, oRow As Long ' to count
Dim iText As String
Dim PIN As String, oPin As String
Dim Business As String, Account As String, Resp As String, SN As String, Loc
As String, Flex As String
Dim V1 As String, V2 As String, V3 As String ' string as all have ()

Close

ReadDBInfo
'The above function gives the variable "DTBFile" a value, i.e. a path and
file name.

IFile = DTBFile
' open the files
Open IFile For Input As #1


' read the input file
' the line count is for test purposes only to enable a stop
While Not EOF(1)
Line Input #1, iText
iRow = iRow + 1

' discard unwanted rows
If InStr(iText, "Detail Trial Balance") <> 0 Then GoTo nextRow
If InStr(iText, "Year to date as of") <> 0 Then GoTo nextRow
If InStr(iText, "Accounting Flexfield") <> 0 Then GoTo nextRow
If InStr(iText, "Buisness Range:") <> 0 Then GoTo nextRow
If InStr(iText, "-----------------------") <> 0 Then GoTo nextRow
If InStr(iText, "Business:") <> 0 Then GoTo nextRow
If Mid(iText, 37, 7) = " " Then GoTo nextRow
'If InStr(iText, "-----------------------") <> 0 Then GoTo nextRow

'only interested in lines over 100 characters
If Len(iText) > 100 Then
' set values
Flex = Mid(iText, 35, 26)
Business = Left(Flex, 2)
Resp = Mid(Flex, 4, 3)
Account = Mid(Flex, 8, 4)
SN = Mid(Flex, 13, 2)
Loc = Mid(Flex, 16, 3)
PIN = Right(Flex, 7)

V1 = fnRepBrac(Mid(iText, 76, 18))
V2 = fnRepBrac(Mid(iText, 95, 18))
V3 = fnRepBrac(Mid(iText, 114, 18))

' output the required values to the new file
oRow = oRow + 1
' write the data into the table
With rs
.AddNew
!Flex = Flex
!Business = Business
!Resp = Resp
!Account = Account
!SN = SN
!Loc = Loc
!PIN = PIN
!Begining = V1
!Activity = V2
!Ending = V3
.Update

End With


End If
nextRow:

Wend

' close
Set rs = Nothing
Set db = Nothing

Close
Debug.Print oRow; " rows loaded. Started " & sTime & " end " & Now() & " " &
Format(Now() - sTime, "hh:mm:ss")

Set db = CurrentDb
Set rs2 = db.OpenRecordset("DetailTB")

RecCount = CStr(rs2.RecordCount)
rs2.Close
Set db = Nothing

retval = MsgBox("File for Detailed TB processed. " + RecCount + " rows
loaded. Click OK to consolidate data into standard format X PIN.",
vbInformation + vbOKOnly, "Load DetailTB")

DoCmd.OpenQuery "qGroupExceptPin", acNormal, acReadOnly

DoCmd.OpenQuery "qLikeOldData", acNormal, acReadOnly

DoCmd.OpenQuery "qAppendOldFormat", acNormal, acReadOnly

DoCmd.Close acQuery, "qGroupExceptPin", acSaveNo

DoCmd.Close acQuery, "qLikeOldData", acSaveNo

Set db = CurrentDb
Set rs2 = db.OpenRecordset("ImportOneMonth")

RecCount = CStr(rs2.RecordCount)
rs2.Close
Set db = Nothing

retval = MsgBox("Consolidation of Detailed TB processed. " + RecCount + "
rows loaded. Click OK to return to the form and select a month to load the
data into.", vbInformation + vbOKOnly, "Consolidate DetailTB")

DoCmd.Echo True
DoCmd.SetWarnings True

End Function
 
J

Jim Burke in Novi

Actually there are times where a command button can lose it's association
with it's event procedures (e.g. if you cut and paste the control and rename
it to it's origianl name). GO to the control's properties, go to the Events
tab, and check to make sure that you see [Event Procedure] next to the Click
event. If not you just need to re-establish the command-event association by
clicking next to the CLick event, then click on the ellipses and it should
then show you the event proc. I've had this happen a few times.
 
G

Graeme

Unfortunately this isn't the problem. I had already checked but in addition I
created a new button and event procedure. Nothing happens when I click it.

The code it is trying to call is in a standard module and works if I play
the code from that location.

If I try to play the code from the event procedure it opens a macro
selection window and lets me choose from the various procedures that have
been written.

Thanks for tip though, that was a problem on an earlier issue!

Jim Burke in Novi said:
Actually there are times where a command button can lose it's association
with it's event procedures (e.g. if you cut and paste the control and rename
it to it's origianl name). GO to the control's properties, go to the Events
tab, and check to make sure that you see [Event Procedure] next to the Click
event. If not you just need to re-establish the command-event association by
clicking next to the CLick event, then click on the ellipses and it should
then show you the event proc. I've had this happen a few times.

Graeme said:
I've created a an event procedure to run when a button in a form is clicked.

It basically calls a sub / function from a standard module.

The problem I have is that i can't get the code to run when the button is
pressed.

I've run the code from within VBE and it works fine.

Can anyone help?

Event Procedure:

Private Sub LoadDetailTB_Click()
Call ReadDetailTB
End Sub

It calls the following Sub / Function (I've tried it as both and it runs in
both instances):

Function ReadDetailTB()
' import a large txt file into Access ready and convert to old skool report
format
' to keep it simple the table will be predefined as DetailTB

Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim retval, SQL As String
Dim sTime As Date

DoCmd.Echo False
DoCmd.SetWarnings False

DoCmd.RunSQL ("Delete * from DetailTB")

sTime = Now()
Set db = CurrentDb
Set rs = db.OpenRecordset("DetailTB", dbOpenDynaset)

Dim IFile As String
Dim iRow As Long, oRow As Long ' to count
Dim iText As String
Dim PIN As String, oPin As String
Dim Business As String, Account As String, Resp As String, SN As String, Loc
As String, Flex As String
Dim V1 As String, V2 As String, V3 As String ' string as all have ()

Close

ReadDBInfo
'The above function gives the variable "DTBFile" a value, i.e. a path and
file name.

IFile = DTBFile
' open the files
Open IFile For Input As #1


' read the input file
' the line count is for test purposes only to enable a stop
While Not EOF(1)
Line Input #1, iText
iRow = iRow + 1

' discard unwanted rows
If InStr(iText, "Detail Trial Balance") <> 0 Then GoTo nextRow
If InStr(iText, "Year to date as of") <> 0 Then GoTo nextRow
If InStr(iText, "Accounting Flexfield") <> 0 Then GoTo nextRow
If InStr(iText, "Buisness Range:") <> 0 Then GoTo nextRow
If InStr(iText, "-----------------------") <> 0 Then GoTo nextRow
If InStr(iText, "Business:") <> 0 Then GoTo nextRow
If Mid(iText, 37, 7) = " " Then GoTo nextRow
'If InStr(iText, "-----------------------") <> 0 Then GoTo nextRow

'only interested in lines over 100 characters
If Len(iText) > 100 Then
' set values
Flex = Mid(iText, 35, 26)
Business = Left(Flex, 2)
Resp = Mid(Flex, 4, 3)
Account = Mid(Flex, 8, 4)
SN = Mid(Flex, 13, 2)
Loc = Mid(Flex, 16, 3)
PIN = Right(Flex, 7)

V1 = fnRepBrac(Mid(iText, 76, 18))
V2 = fnRepBrac(Mid(iText, 95, 18))
V3 = fnRepBrac(Mid(iText, 114, 18))

' output the required values to the new file
oRow = oRow + 1
' write the data into the table
With rs
.AddNew
!Flex = Flex
!Business = Business
!Resp = Resp
!Account = Account
!SN = SN
!Loc = Loc
!PIN = PIN
!Begining = V1
!Activity = V2
!Ending = V3
.Update

End With


End If
nextRow:

Wend

' close
Set rs = Nothing
Set db = Nothing

Close
Debug.Print oRow; " rows loaded. Started " & sTime & " end " & Now() & " " &
Format(Now() - sTime, "hh:mm:ss")

Set db = CurrentDb
Set rs2 = db.OpenRecordset("DetailTB")

RecCount = CStr(rs2.RecordCount)
rs2.Close
Set db = Nothing

retval = MsgBox("File for Detailed TB processed. " + RecCount + " rows
loaded. Click OK to consolidate data into standard format X PIN.",
vbInformation + vbOKOnly, "Load DetailTB")

DoCmd.OpenQuery "qGroupExceptPin", acNormal, acReadOnly

DoCmd.OpenQuery "qLikeOldData", acNormal, acReadOnly

DoCmd.OpenQuery "qAppendOldFormat", acNormal, acReadOnly

DoCmd.Close acQuery, "qGroupExceptPin", acSaveNo

DoCmd.Close acQuery, "qLikeOldData", acSaveNo

Set db = CurrentDb
Set rs2 = db.OpenRecordset("ImportOneMonth")

RecCount = CStr(rs2.RecordCount)
rs2.Close
Set db = Nothing

retval = MsgBox("Consolidation of Detailed TB processed. " + RecCount + "
rows loaded. Click OK to return to the form and select a month to load the
data into.", vbInformation + vbOKOnly, "Consolidate DetailTB")

DoCmd.Echo True
DoCmd.SetWarnings True

End Function
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run time error in VBA Code 1
Data Type Conversion error 5
Call public procedure by variable 5
Split not working correctly 2
link all tables but one using VBA 1
Cannot get Seek method on linked table to work. 7
Converting Ole Objects containing linked addresses into hyperlinks 7
Timer event not working. 4

Top