Replies

Fantastic, I’m glad to have helped.

The fact that the code fixed the import file meant that there was a problem with record terminators.

Stewart

try using the NZ function rather than the iif statement that you are using

ie
Private Sub Report_Activate()
Dim var24Hrs As Integer
Dim var48Hrs As Integer
Dim var72Hrs As Integer
Dim var1Wk As Integer
Dim varMoreThan1Wk As Integer

var24Hrs = nz([24 Hrs]), 0)
var48Hrs = nz([48 Hrs]), 0)
var72Hrs = nz([72 Hrs]), 0)
var1Wk = nz([1 Week]), 0)
varMoreThan1Wk = nz([More Than 1 Week]), 0)

[24 Hrs] = nz([EIR Total],0) – var48Hrs – var72Hrs – var1Wk – varMoreThan1Wk

End Sub

what datatypes are the fields, the iif statement you posted and the nz function just check for null. Are the fields numeric or is it possible that you are passing a string value for a calculation.

Trudi,

There are no stupid questions. CR = Carriage Return. LF = Line Feed. Access seems to need this combination to determine the end of a record for text import.

To check what the text file is using for record terminators I’ve attached a small db. Open your text file and copy a few lines. Paste them into the first record in the table and run the function read in the module. the instances of lf or cr are shown in the debug window 10 = lf 13 = cr.

The sample data will display 13 13 10 for each record and access will not be able to import this file as there is not a clean record terminator that it can understand.

The function for fixing a text file is as follows.

Cheers
Stewart

Function text_clean(InDirectory As String, Infile As String, Optional OutDirectory As String = “”, Optional Outfile As String = “”)

‘#####################################################################################
‘# Function takes input file and reads it, converting Line Feeds to Carriage Returns #
‘# This in effect cleans up files from the VAX and makes them easier to import into #
‘# Microsoft Access 97. #
‘# #
‘# If the Output File Name is not specified then the original file is overwritten, #
‘# Else the new file created is specified by OutFile #
‘# #
‘# #
‘# #
‘# #
‘# This program assumes 8.3 format, it will work with others, if the file name has #
‘# two “.” in the name, then there may be some unexpected results #
‘#####################################################################################

On Error GoTo Error_Text_Clean
text_clean = “”

Dim x As Integer
Dim y As Integer
Dim Message As String
Dim a_char As String
Dim a_line As String
Dim overwrite As Boolean
Dim Path_InFile As String
Dim Path_OutFile As String
Dim Path_FileDelete As String
Dim filelength As Long
Dim currentrecord As Long
Dim meterReturn As Variant

overwrite = False

x = FreeFile

If Dir(InDirectory & Infile) = “” Then
MsgBox “Input File ” & Infile & ” ,In Directory: [” & InDirectory & “] Does Not Exist, Please check import file has been created and try again”, vbCritical
Exit Function
End If

Path_InFile = InDirectory & Infile
Open Path_InFile For Input As x

y = FreeFile

If Len(Outfile) + Len(OutDirectory) = 0 Then
overwrite = True
End If

If overwrite = True Then
Path_OutFile = InDirectory & “temp.txt”
Else
Path_OutFile = OutDirectory & Outfile
End If

Open Path_OutFile For Output As y
currentrecord = 0
filelength = LOF(x)

meterReturn = SysCmd(acSysCmdInitMeter, “Checking Import Text File Format”, filelength)

Do While Not EOF(x)
Do
a_char = Input(1, #x)
currentrecord = currentrecord + 1
meterReturn = SysCmd(acSysCmdUpdateMeter, currentrecord)

If InStr(vbCrLf, a_char) = 0 Then
a_line = a_line & a_char
End If

Loop While a_char vbLf
Print #y, a_line
a_line = “”
Loop

Close x
Close y

If overwrite = True Then
Path_FileDelete = InDirectory & Left$(Infile, InStr(Infile, “.”)) & “old”
If Dir(Path_FileDelete) > “” Then ‘(delete .old file if it exists)
Kill Path_FileDelete
End If
Name Path_InFile As InDirectory & Left$(Infile, InStr(Infile, “.”)) & “old” ‘rename existing input file to .old
Name Path_OutFile As Path_InFile ‘rename output file to input file name
End If

text_clean = Path_OutFile
meterReturn = SysCmd(acSysCmdClearStatus)
Exit Function

Error_Text_Clean:

Close x
Close y

Message = “An Error Occured while processing ” & Infile & vbCr & “Import File May Be Corrupted”
MsgBox Message, vbCritical
MsgBox “Please Write Down this Error Number and Description and seek assistance” & vbCr & “Error Number ” & Err.Number & vbCr & “Error Description ” & Err.Description, vbExclamation
text_clean = “”
meterReturn = SysCmd(acSysCmdClearStatus)
End Function

Hi Trudi,

The user downloads the file, how? if this is via ftp you may wish to check the transfer protocol that the user selects. I’ve got a few users who occasionally transfer their text files as binary rather than as ascii. this generally creates a problem where the record terminators are not complete so Access return a message RECORD LENGTH TO LONG.

Check the record terminators to ensure that there is a CR/LF pair, if one is missing access gets confused, versions after Access 2 anyway.

I’ve got some code that will process your text file and save a new version with a clean record terminator if you want it.

I have processed the text file pre import in some applications but it does add to the processing time.

If the problem only exist for one user, the problem relates to either what they are doing or their machine. I’d try to establish the cause before coming up with esoteric workarounds.

Stewart

You said “if two people have the db open to add a number, both instances will see the most recent number, add 1 to it and give the same number. if one finishes and clicks ok to enter the job number before the other finishes (at least by a few seconds) when the second accepts the data, it will say the job number is already in use.”

This seems to be the problem. The job number is generated for the data entry and not immediately posted to allow the second user to obtain the next number in the sequence. You can use the before insert event to populate the job num and post the record.

A better solution is or leave the Jobnum blank until post and then get the next number in the sequence. Use the before update event to check if the job num is populated and if not populated grab the next number in the sequence.

This will allow you to maintain a sequence without any gaps and without having two users recording the same job number. As one of the other posts said your sequence of job numbers probably should be in a seperate table, just to make life easier for you.

Is rst!ColumnCount equal to 3?

It’s a shame I didn’t read you rpost sooner or I’d have saved you a couple of days stumbling.

Glad to of been of some assistance, even if it was to late.

Cheers
Stewart

go to the command prompt,
type set
press enter.

Can you see the required username there?

if so you can use the
environ(“username”) statement where username is the environment variable I’m interested in for this example.

You’ll probably need to make the subforms unbound and post the data entry to the tables via code. It’s going to take a bit more effort but you should be able to get the effect and functionalit that you need.

Stewart

Paul,

Ideally the datasource for the report & or form would be the query that you created that contains the concatenated expression. Alternately if this is not practical consider modifying the datasource that you are using to include the concatenated expression so it is displayed in conjunction with each record.

Using the on current event could populate an unbound field fairly readily but that assumes that either the required fields are present in the form or the expression is build in code from the related tables.

Including the expression in the query the form is based on is probably the simplest solution.

Stewart

You can also trap the error that occurs. This allows you to get a meaningfull error when something unexpected happens.

eg surround your code in a procedure with the following.

at start.
On Error goto Error_trap

{code in here)

Exit_Procedure:
Exit Sub
Error_Trap:
Select case err
Case 2501’Report open canceled.
‘do nothing, we accept this error.
Case Else
msgbox Error,vbInformation,Err & “{procedure Name}”
end select
resume Exit_Procedure ‘Quit after error.
This allows you to receive an error description in the msgbox and the error number for trapping if necessary and the procedure where the error occured as the title for the msgbox.

Never blindly disable error trapping, as the ability to trap errors and deal with thenm is the primary advantage of using vba over macros.

use UNC paths to prevent the problems with drive mappings.

I find that at the start of a procedure if you set the timer interval =0 and then set it back to whatever it was on procedure exit that it doesn’t interfere with my general processing. I do this a lot as I use the timer to check for a log out call in pretty much all my applications.

in the line

If Me(“Filter” & intCounter) < "" Then

what is "<". I don't suppose that you can save the attachment as a 97 db so I can look at it?

ok, this should fix it.

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer, a
Dim frm As Form, ctl As Control
Dim varItm As Variant
Set frm = Forms!frmFilter
Set ctl = frm!Filter1

For Each varItm In ctl.ItemsSelected
    a = ctl.ItemData(varItm) & "," & a
Next varItm

For intCounter = 1 To 5
    If Me("Filter" & intCounter)  "" Then
        strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] "
          If intCounter = 1 Then
            strSQL = strSQL & " IN(" & Chr(34) & a & Chr(34) & ") And "
        Else
            strSQL = strSQL & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) _
& " And "
        End If
    End If
Next

If strSQL  "" Then     ' Strip Last " And ".
    strSQL = Left(strSQL, (Len(strSQL) - 5))     ' Set the Filter property.
    Reports![rptCustomers].Filter = strSQL
    Reports![rptCustomers].FilterOn = True
End If

End Sub

I’m not the same Stewart Tanner, although it was interesting. I’m in Victoria and am a bit to old to be a Uni student.