Bad File Name Or Number

[slimpickens]

Hi guys & gals

I have written some code for my worksheet but I keep getting the above error message. I have tried using Help to see if I can get it sorted out but I am not getting anywhere. Most of the code I found in a visual basic manual and I changed it around to suit my purposes but since I have limited knowledge of VB, I find myself at a loss. Would anyone care to have a look at the code and probably point me in the right direction????


Drop me a line:- Randycas@yahoo.com

 

[Juan Pablo González]

I think you could have better results if you posted the not working code and tell us where exactly is it showing the error.

 

[slimpickens]

I posted all the code since the line that is giving the problem is

""#gFileNum, gCurrentRecord, gPerson""

This goes throughout the coding


Dim w As Workbook
Dim gPerson As PolicyInfo
Dim gFileNum As Integer
Dim gRecordLen As Long
Dim gCurrentRecord As Long
Dim gLastRecord As Long


Public Sub SaveCurrentRecord()
'Fill gPerson with the currently displayed data
gPerson.Polno = Polno.Text
gPerson.Refno = Refno.Text
gPerson.Action = Action.Text
gPerson.Comments = Comments.Text

'Save gPerson to the current record
Put #gFileNum, gCurrentRecord, gPerson

End Sub
Public Sub ShowCurrentRecord()
'Fill gPerson with data of current record
Get #gFileNum, gCurrentRecord, gPerson

'Display gPerson
Polno.Text = Trim(gPerson.Polno)
Refno.Text = Trim(gPerson.Refno)
Action.Text = Trim(gPerson.Action)
Comments.Text = Trim(gPerson.Comments)

'Display the current record number in the caption of the form

Cancellations.Caption = "Record " + _
Str(gCurrentRecord) + "/" + _
Str(gLastRecord)

End Sub
Private Sub Form_Load()
'Calculate the length of the record
gRecordLen = Len(gPerson)
'Get the next available file number
gFileNum = FreeFile

'Open the file for random access. If the file
'does not exist then it is created.

Open "CANCELLATIONS" For Random As gFileNum Len = gRecordLen

'Update gCurrentRecord.
gCurrentRecord = 1

'Find what is the last record number of the last record

gLastRecord = FileLen("CANCELLATIONS.XLS") / gRecordLen

'If the file was just created, the update gLastRecord to 1

If gLastRecord = 0 Then
gLastRecord = 1
End If

'Display Current Record
Show CurrentRecord

End Sub

Private Sub Action_Enter()
ActiveControl.DropDown

End Sub

Private Sub cmdExit_Click()
'Save the current record
SaveCurrentRecord
'Close the File
Close #gFileNum
For Each w In Application.Workbooks
w.Save
Next w
Application.Quit

End

End Sub

Private Sub cmdNew_Click()
'Save the current record
SaveCurrentRecord
'Add a new blank record
gLastRecord = gLastRecord + 1
gPerson.Polno = ""
gPerson.Refno = ""
gPerson.Action = ""
gPerson.Comments = ""


'Update gCurrentRecord
gCurrentRecord = gLastRecord

'Display the record that was just created.
ShowCurrentRecord

'Give the focus to the txtName field.
Polno.SetFocus

End Sub
Private Sub cmdNext_Click()
'If the current record is the last record,
'beep and display an error message. Otherwise,
'save the current record and skip to the
'next record.

If gCurrentRecord = gLastRecord Then
Beep
MsgBox "End of File!", vbExclamation
Else
SaveCurrentRecord
gCurrentRecord = gCurrentRecord + 1
ShowCurrentRecord
End If
'Give focus to the polno field
Polno.SetFocus

End Sub
Private Sub cmdPrevious_Click()
'If the current record is the first
'beep and display an error message. Otherwise
'save the current record and go the the previous record
If gCurrentRecord = 1 Then
Beep
MsgBox "Beginning of File!", vbExclamation
Else
SaveCurrentRecord
gCurrentRecord = gCurrentRecord - 1
ShowCurrentRecord
End If
Polno.SetFocus

End Sub


Private Sub cmdSearch_Click()
Dim NameToSearch As String
Dim Found As Integer
Dim RecNum As Long
Dim TmpPerson As PolicyInfo

'Get the name to search from the user
NameToSearch = InputBox("Search for:", "Search")

'If the user did not enter a name, exit from this procedure
If NameToSearch = "" Then
'Give the focus to the polno.
Polno.SetFocus
'Exit this porcedure.
Exit Sub
End If

'Convert the name to be searched to upper case.
NameToSearch = UCase(NameToSearch)

'Initialize the Found flag to False
Found = False

'Search for the name the user entered.

For RecNum = 1 To gLastRecord
Get #gFileNum, RecNum, TmpPerson
If NameToSearch = UCase(Trim(TmpPerson.Refno)) Then
Found = True
Exit For
End If
Next

'If the name was found, display the record of the found name

If Found = True Then
SaveCurrentRecord
gCurrentRecord = RecNum
ShowCurrentRecord
Else
MsgBox NameToSearch + " not found!"
End If
'Give the focus to the Polno
Polno.SetFocus

End Sub

 

[Ivan F Moala]

Hi Slimpickens

how have you configured the code to run ?
and is this the complete code.

the reason i ask is that this is obviously
VB code and is designed to run similar to
a VBA Userform eg.

Private Sub Form_load()

VBA eq Private Sub UserForm_Initialize()

Running the code via a userform ini would
set up your defined Freefile variable.

But I think you are also missing some Data Type declarations as you have;
Dim gPerson As PolicyInfo but have no
declarations for this

would suggest that there should be something
along these lines....

Private Type PolicyInfo
Polno As String
Refno As String
Action As String
Comments As String
End Type


Post for further help and how you have configured this to run.

Ivan

 

[slimpickens]

Thanks for the help Ivan,

I made declarations for PolicyInfo , I have them in a module, do I need to move them.

I am not quite sure what you mean by "configuring the code" since I copied the code from a book, just making some adjustments. I really have absolutely no idea what is going on when coding starts to get really technical but I am trying to learn. Though it is hard. I have been trying to find a manual which deals directly with excel and Vb, but the bookstores here do not have any.

I will try the code for the Intialize event and see what happens, while I wait to locate a manual.

Carol

 

[Ivan F Moala]

On 2002-03-28 08:57, slimpickens wrote:
Thanks for the help Ivan,

I made declarations for PolicyInfo , I have them in a module, do I need to move them.

I am not quite sure what you mean by "configuring the code" since I copied the code from a book, just making some adjustments. I really have absolutely no idea what is going on when coding starts to get really technical but I am trying to learn. Though it is hard. I have been trying to find a manual which deals directly with excel and Vb, but the bookstores here do not have any.

I will try the code for the Intialize event and see what happens, while I wait to locate a manual.

Hi Carol
Have you used this code for a Userform ?
Thats what the code was designed for.

When I said configure I meant adjusted.
As I said before this code was meant for
a VB Form and there are slight diff in VB
and VBA syntax.

The Type declarations should reside in the
same Module / userform as the code you gave
above.

Ivan

Post if you require assistance

 

[slimpickens]

I will try that and get back to you Ivan. Sorry I took so long to answer, but we were having some problems with the Internet here at work.

Carol