Replies

Hans,

Here is a stripped version of what we use. Without a huge amount of records in a text file, the error can not be recreated. However you may be able to look at the code and give recommendations to improve performance and lessen the chance of getting an error. Any suggestions are GREATLY appreciated.

Regards,
Drew

The last test I ran opened a form and populated appoximately 10 captions with the number of records in 10 different tables. Originally I used DCount for this and had no problems. It worked fast with no delay. When trying to make my code more efficient, I used the recordset approach. After that, the form took 3-5 seconds to open (can’t remember how many records were in each table…if that matters).

Thanks for your help. I will try using the recordset approach on this one. May just live with the delay.
Regards,
Drew

The subdatasheet is an intermediate table for data that I get from our PDM system, which is pasted from my clipboard after coping from a system report. After it’s pasted, I select the import button and the data cascades into multiple tables. For now, I don’t need to insert data into some of the tables, so I only select 12 of the 35 columns (script does the rest). It would be nice to have a convenient button to select the columns I’m interested in.

Thanks,
Drew

Perfect!
Thank you

Perfect…Thank you

Thanks!

Mark,

I overlooked the fact that I had a setfocus command used on an open event that set focus to a control on Page1.
I changed this and it now pops up on Page2.

Thanks for the help,
Drew

Yes…
Page2 (Page Index = 0)
Page1 (Page Index = 1)
Page3 (Page Index = 2)

Still defaults to Page1.

Regards,
Drew

Hans,

Forget about my last question. Somewhere on the lines I accidentally remove the ‘On Error Goto’ line. I put this back in an works perfectly.

Thank you very much for your help.
Drew

Thanks Hans.

I tried this and am now getting a 3022 error message, so I added 3022 to the case, but am still getting the 3022 error. Am I doing something wrong?

ErrHandler:
Select Case Err
Case 3058, 3022 ‘ Duplicate key
‘ Continue with next statement in code
Resume Next
Case Else ‘ Other errors
‘ Report error and exit
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Select
End Sub

I had also thought that I could do a check before a new record was added to the keyed table by using a DCount with the 3 key fields in the table as criteria. However, it seem like their should be a better way than this.

Thanks,
Drew

Sorry.

Hans,

Does this mean that you can’t go back in a text file? Similar to a recordset.MovePrevious.

Attached is the code that I’m trying to get to work. You’ll probably be able to decifer this better than I could explain it. What I’m trying to do is find the end of a record by finding a double-quote at the end of one line input (previous) and a double quote at the beginning of the next line input (current). The problem I’m having, is that if a line in the text file does not start with a double-quote, the string it pulls out is an empty string. Does Line Input require double-quotes at the beginning of each line? If so, is there a work around? (Microsoft help on this is very limited on this).

Thanks!
Drew

Must line input always be used when working with text files? And, is their anyway to set the pointer to the previous line (or is it pointer forward only)?

Thanks,
Drew

Hans,

Logically, I think I have a way to parse this out, but I have a VBA question.
When opening a text file in VBA and using loops with it, how does VB know what location in the file you are at? I see that your code continues to loop by using the string found in Line Input #, but I don’t know how it knows to go to the next line. In addition, is their a way to use Left(), Mid(), and instr() functions with text files?

Thanks,
Drew

I can’t agree with you more. 🙂
We are a small division of a large corporate comany. Our PDM system is nearly 5 years old and service packs have not been installed due to going to the next version of the software incorporating all other business units. The corporate deadline for this keeps on getting pushed out. In the mean time, it makes it very difficult for myself and others to report on what we put into our system which is the reason why I continue to use extracts along with Access.
For my problem, I’ll either try to clean the extracts manually or just leave the problem fields out.

Thank you very much for your help.
Regards,
Drew