Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-29-2007, 05:13 PM   #1
RSW
Newly Registered User
 
Join Date: May 2006
Posts: 178
Thanks: 13
Thanked 0 Times in 0 Posts
RSW is on a distinguished road
Question Parser help needed

I really hope this is easy...I inherited the maintenance of an Access database from someone...I am OK with Access, but a complete novice at VBA.

My database needs to read manufacturing log files. Here's a very simplistic idea of what the files USED to look like:

Partition number: 1
Serial number: 1001
_________________
Partition number: 1
Placement #: 1
Part#: 12345
Partition number: 1
Placement #: 2
Part#: 23456

However they have changed the process so that the equipment builds several serial numbers at once, in different partitions. So a log file might look like this:

Partition number: 1
Serial number: 1001
Partition number: 2
Serial number: 1002
Partition number: 3
Serial number: 1003
_________________
Partition number: 1
Placement #: 1
Part #: 12345
Partition number: 2
Placement #: 1
Part #: 12345
etc...

The point of this database is to link Part # with Serial number. Since there was only one serial number per log file before, there is a
Dim Tile_Serial as String
statement in the front of the VBA code, as the parser expects to read one and only one serial number.

I am hoping to find out two things:

1. How do I change Tile_Serial into an array or something so that I can match parts to serial numbers? The serial numbers per build can vary between one and three.

2. Assuming I make it an array, how will it be treated differently from string variables? They are all I have so far. Basically I need to ensure that as the parser goes down the log file and sees a "Partition number", it stores that somehow so it will be linked to the Serial number through that Partition number.

I am really, really hoping this is easy to update. I just got put on an assignment I'm not yet qualified for, and it's taken me forever to decipher what this code actually does. Any help anyone can provide will be really appreciated!!

RSW is offline   Reply With Quote
Old 01-29-2007, 06:18 PM   #2
Moniker
VBA Pro
 
Join Date: Dec 2006
Location: Dallas
Posts: 1,567
Thanks: 0
Thanked 10 Times in 9 Posts
Moniker will become famous soon enough Moniker will become famous soon enough
What format is that log file in? You really should need very little programming at all to do this, as it can probably all be done in tables and queries, but it depends on the format of that file.
__________________
~Moniker

(If you've been helped by me or anyone else, please add to their reputation by clicking the "scales" icon in the upper-right.)
Moniker is offline   Reply With Quote
Old 01-30-2007, 09:09 AM   #3
RSW
Newly Registered User
 
Join Date: May 2006
Posts: 178
Thanks: 13
Thanked 0 Times in 0 Posts
RSW is on a distinguished road
Quote:
Originally Posted by Moniker
What format is that log file in? You really should need very little programming at all to do this, as it can probably all be done in tables and queries, but it depends on the format of that file.
Unfortunately, it is a flat text file. The parser is already all set up, I just need to update it for handling multiple serial numbers at once.

RSW is offline   Reply With Quote
Old 01-30-2007, 10:41 AM   #4
Moniker
VBA Pro
 
Join Date: Dec 2006
Location: Dallas
Posts: 1,567
Thanks: 0
Thanked 10 Times in 9 Posts
Moniker will become famous soon enough Moniker will become famous soon enough
Do you always have the list of Partition Numbers and Serial Numbers at the top, and then the list of Partition, Placement, and Part second? In other words, is it a giant list like this:

Partition number: 1
Serial number: 1001
Partition number: 2
Serial number: 1002
Partition number: X
Serial number: X

And then this part starts:

Partition number: 1
Placement #: 1
Part #: 12345
Partition number: x
Placement #: x
Part #: x

If that's the case, this will become fairly straightforward.
__________________
~Moniker

(If you've been helped by me or anyone else, please add to their reputation by clicking the "scales" icon in the upper-right.)
Moniker is offline   Reply With Quote
Old 01-30-2007, 11:50 AM   #5
RSW
Newly Registered User
 
Join Date: May 2006
Posts: 178
Thanks: 13
Thanked 0 Times in 0 Posts
RSW is on a distinguished road
Exactly! I thought it would be straightforward, I just have never done this before and I am looking at someone else's code.
RSW is offline   Reply With Quote
Old 02-01-2007, 01:45 PM   #6
RSW
Newly Registered User
 
Join Date: May 2006
Posts: 178
Thanks: 13
Thanked 0 Times in 0 Posts
RSW is on a distinguished road
Sorry to bump...can anyone assist?
RSW is offline   Reply With Quote
Old 02-01-2007, 10:05 PM   #7
WayneRyan
AWF VIP
 
Join Date: Nov 2002
Location: Camarillo, CA
Posts: 7,065
Thanks: 5
Thanked 54 Times in 52 Posts
WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough
RSW,

I hate getting data like that!

If you are trying to get the unique Partition/Serial/Part/Placement it
can be surprisingly easy.

Code:
Dim rst As DAO.Recordset
Dim strPartition As String
Dim strSerial    As String
Dim strPart      As String
Dim strPlacement As String

Dim strBuffer As String

Set rst = CurrentDb.OpenRecordset("Select * From YourTable")

Open "C:\SomeFile.txt" For Input As #1

Line Input #1, strBuffer
While Not EOF(1)
   If InStr(strBuffer, "Partition") > 0 Then
      strPartition = Mid(strBuffer, Instr(strBuffer, ": ") + 2)
   ElseIf InStr(strBuffer, "Serial") > 0 Then
      strSerial = Mid(strBuffer, Instr(strBuffer, ": ") + 2)
      '
      ' Save after every new serial #
      '
      rst.AddNew
      rst!Partition = strPartition
      rst!Serial = strSerial
      rst!Part = strPart
      rst!Placement = strPlacement
      rst.Update
   ElseIf InStr(strBuffer, "Part") > 0 Then
      strPart = Mid(strBuffer, Instr(strBuffer, ": ") + 2)
   ElseIf InStr(strBuffer, "Placement") > 0 Then
      strPlacement = Mid(strBuffer, Instr(strBuffer, ": ") + 2)
   End If
   Line Input #1, strBuffer
   Wend
Close #1
hth,
Wayne

WayneRyan is offline   Reply With Quote
Old 02-02-2007, 05:34 PM   #8
RSW
Newly Registered User
 
Join Date: May 2006
Posts: 178
Thanks: 13
Thanked 0 Times in 0 Posts
RSW is on a distinguished road
I will try something like that and let you know! Thanks!
RSW is offline   Reply With Quote
Old 02-03-2007, 06:17 PM   #9
RSW
Newly Registered User
 
Join Date: May 2006
Posts: 178
Thanks: 13
Thanked 0 Times in 0 Posts
RSW is on a distinguished road
Wayne,

I am not sure I understand the code (remember I am a VBA novice).

I understand how it adds records for the Partition, Serial, Part and Placement. However I am not sure I understand where/how the one-to-one relationship between Partition and Serial is stored. I don't care about Partition--only Serial--but unfortunately after listing their relationships in the beginning of the log file, the Serial numbers are not mentioned after that. Can you explain what I am missing?

Thanks!!
RSW is offline   Reply With Quote
Old 02-03-2007, 07:53 PM   #10
WayneRyan
AWF VIP
 
Join Date: Nov 2002
Location: Camarillo, CA
Posts: 7,065
Thanks: 5
Thanked 54 Times in 52 Posts
WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough
R,

The example posted only dealt with ONE simple table. Any further clarification
will depend on the existing table structure(s) within your database.

Wayne
WayneRyan is offline   Reply With Quote
Old 02-04-2007, 11:27 PM   #11
Bat17
Newly Registered User
 
Join Date: Sep 2004
Location: Maidstone, Kent. UK
Posts: 1,687
Thanks: 0
Thanked 6 Times in 5 Posts
Bat17 is on a distinguished road
try this variation on Wayne's code. it assumes two tables that will be joined on 'Partition' and that in your text file the will be at least one blank line between the two sets of data.
Code:
Dim rst As DAO.Recordset
Dim strPartition As String
Dim strSerial    As String
Dim strPart      As String
Dim strBuffer As String

Set rst = CurrentDb.OpenRecordset("Select * From tblText")

Open "H:\temp\texttest.txt" For Input As #1

Line Input #1, strBuffer
While Not Len(strBuffer) = 0
   If InStr(strBuffer, "Partition") > 0 Then
      strPartition = Mid(strBuffer, InStr(strBuffer, ": ") + 2)
   ElseIf InStr(strBuffer, "Serial") > 0 Then
      strSerial = Mid(strBuffer, InStr(strBuffer, ": ") + 2)
      '
      ' Save after every new serial #
      '
      rst.AddNew
      rst!Partition = strPartition
      rst!Serial = strSerial
      rst.Update
    End If
   Line Input #1, strBuffer
Wend
' fill second table
Set rst = CurrentDb.OpenRecordset("Select * From tblText2")
While Not EOF(1)
    Line Input #1, strBuffer
   If InStr(strBuffer, "Partition") > 0 Then
      strPartition = Mid(strBuffer, InStr(strBuffer, ": ") + 2)
   ElseIf InStr(strBuffer, "Part #:") > 0 Then
      strPart = Mid(strBuffer, InStr(strBuffer, ": ") + 2)
      '
      ' Save after every new Part #
      '
      rst.AddNew
      rst!Partition = strPartition
      rst!Part = strPart
      rst.Update
    End If
Wend
Set rst = Nothing
Close #1
Peter
Bat17 is offline   Reply With Quote
Old 02-05-2007, 08:02 AM   #12
RSW
Newly Registered User
 
Join Date: May 2006
Posts: 178
Thanks: 13
Thanked 0 Times in 0 Posts
RSW is on a distinguished road
Thanks everyone! Really appreciate the help.

RSW is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
keywords needed to populate db letsomebodyknow Queries 0 12-09-2006 07:40 PM
[SOLVED] Help needed setting up a F1 scorecard NickJ Tables 1 02-12-2006 04:35 AM
Query to fill in blanks within a table? Maybe some other way? robjones23 Queries 11 03-21-2005 03:10 AM
XML Parser juliebme Modules & VBA 4 03-13-2002 10:16 AM




All times are GMT -8. The time now is 11:30 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World