Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hi John

    Thank you again. I understand your posts very well. Your English is very good. In which country are you?

    I am enjoying looking at your solutions and experimenting with them.

    Are the units of machine settings always in imperial system? What about the node <Units>mm/min</Units> in the XML files? What about the "MM" in the machine's name that you subtract?
    The node <Units> is used in some user interface software that converts to values from imperial to metric. The "MM" in machine name has nothing to do with metric. It is a naming convention.

    I have looked at some more XML files that I have received and found that I need to do more extensive error checking on MachineName. I thought this might happen, that is why I added the MachineNo field. This is the field that will link to the rest of the DB and customer table. This field is key to the entire project. It is actually a unique serial number that is assigned during manufacture. Now it appears that some older machine have a slightly different and unpredictable format when extracted from the XML. The final serial number (MachineNo) must always be in the format ####-####

    So I came up with this (from my earlier work).

    Code:
    MachineName = Mid(text, MachineName + 13, StringLen)
    
    ' reformat MachineName by removing non numeric characters
    Dim NewMachineName As String
    NewMachineName = ""
    For counter = 1 To Len(MachineName)
        ' iterate over length of string
        ascVal = Asc(Mid$(MachineName, counter, 1))
        ' find ascii vale of string
        Select Case ascVal
    
            Case 48 To 57
            ' if value in case then acceptable to keep and build new string
            NewMachineName = NewMachineName & Chr(ascVal)
        End Select
    Next counter
        
    ' Now check the result is a valid - should be 8 digits long
    If Len(NewMachineName) < 8 Then
        MsgBox "Invalid Machine Name Format." & vbCr & "The machine name " & MachineName & " format is not recognised." & vbCr & vbCr & _
        "Format should be ####-####" & vbCr & vbCr & "Check the XML file and try again.", vbExclamation, "Import File Error"
        Set f = Nothing
        DoCmd.Close acForm, Me.Name, acSaveNo
        Exit Sub
    
    Else
       'The format is valid so add the missing "-" to create the unique serial number.
       MachineNo = Left([NewMachineName], 4) + "-" + Right([NewMachineName], 4)
    End If
    I am looking at integrating this in to your solution.

  2. #17
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi Stephen!

    A quick reply for now:

    The function below, removes all non numeric characters (exept "-") from the argument and returns True if the result's format is "-####-####"
    (in most provided xml files, the machine name begins with a hyphen)
    Code:
    Function IsMachineName(ByRef strXMLValue As String) As Boolean
        'Returns the strXMLValue with only numeric characters and hyphens.
        'Returns True if the strXMLValue after cleaning has the '-####-####' format
        Dim i As Integer
        Dim c As String * 1
        Dim strRet As String
    
        For i = 1 To Len(strXMLValue)
            c = Mid$(strXMLValue, i, 1)
            If (c Like "[-0-9]") Then
                'is a digit or a "-"
                strRet = strRet & c
            End If
        Next i
        
        strXMLValue = strRet
        'Adapt the criteria ("-####-####") to your requirements
        IsMachineName = (strXMLValue Like "-####-####")
    End Function
    In your code, could be used as follows:
    Code:
    MachineName = Mid(text, MachineName + 13, StringLen)
    
    If IsMachineName(MachineName) Then
        'You have a valid machinename...
        'Import settings...
        '[...]
    End If
    and in the code of frmMachineInfo, just replace the line:
    Code:
    If Len(strMachineName) < 9 Then
    with this:
    Code:
    If Not IsMachineName(strMachineName) Then

    Greetings from Greece,
    John

  3. #18
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hi John

    Thank you for your ideas.

    In fact the Number is supposed to be ####-####. It is not supposed to have the leading hyphen "-"

    This is why I strip all non numeric characters from the number and then re-introduce it in the correct place.

    Not a big problem, I will work it out.

    Have a great weekend.

  4. #19
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    You are welcome! It was my pleasure.

    Good luck with your project!

  5. #20
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hi John

    Thank you again for your help and comments. I found the last DB you sent me really educational. Loved the "on-the-fly" switching between MM and Imperial settings on your form. Lots of juicy stuff to look at.

    The end result is that I have a result with which I am pleased and works nicely. I will continue to tweak the code a bit and tidy up, but at this time I'm happy to announce that a solution has been achieved. Just need to integrate it in to my main DB now and link to customer records.

  6. #21
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151

    Exclamation Error 3075

    Further to this project, it has come on really well and I've been rolling it out for beta testing.

    Whilst carrying out real world testing, I have come across an interesting scenario.

    One of the XML files that attempted to import was named Ro'furbished finishes - XMLMachineAdjustableParams.xml

    When I attempt to import this file I get the following Runtime Error 3075;

    Click image for larger version. 

Name:	Capture.JPG 
Views:	9 
Size:	15.3 KB 
ID:	42333

    Curiously, every time I close and restart the DB and try to import the file again I get the same error 3075, but the settingsID value increments by 1, so the next time that I imported the file after error (after closing the DB and re-opening) the settingsID value will be 11

    If I rename the XML file to Rofurbished finishes - XMLMachineAdjustableParams.xml (remove the ') then the XML file imports perfectly. So obviously the error is caused by the filename. Likely an invalid character in the filename, though the O/S is happy with the filename.

    It's not a huge issue because I can test for a valid filename prior to opening the XML file to import (and maybe rename so that it will import), but I'd like to try and understand what is going on here. Especially the incrementing of the settingsID value increasing.

    Over to the brains trust.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Correct way to import into Access
    By templeowls in forum Access
    Replies: 1
    Last Post: 04-03-2019, 06:22 AM
  2. Replies: 2
    Last Post: 10-29-2018, 12:30 PM
  3. Replies: 11
    Last Post: 12-04-2017, 07:18 AM
  4. File Picker To Only Allow Single File Section
    By jo15765 in forum Programming
    Replies: 2
    Last Post: 05-29-2017, 04:25 PM
  5. Replies: 2
    Last Post: 01-13-2015, 07:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums