UtterAccess Discussion Forums > A Bit More Code Help

UtterAccess Discussion Forums > Microsoft® Access > Access Q and A

kapeller

Sep 21 2008, 04:27 AM

Hi

I seek a bit more help with a small piece of code.

I have data that is entered in a table tblStandardPhrase in this format AQTF1.1 AQTF1.2 and so on and is them used via the code below

It is important that the data is entered in this manner.

If the data is in this format AQTF11 the code below functions correctly.

CODE

    For i = lStart To lEnd
        If Me.Controls(sInitChar & "ChkTRC" & CStr(i)) = -1 Then    'Insert only if the value Yes CodeNonCon CI,
            sSql = "INSERT INTO tblAuditNonConformance (ANCAuditID, ANCRTOID,  ANCNonConTypeID, ANCAuditItem," & _
                   "ANCAuditNonConformance, ANCAuDitType, ANCObservation, ANCAuditSortSequence, ANCCorrectiveAction,  ANCRecAuditDate) " & _
                   "SELECT " & txtIDInAud & " AS IDInAud, " & txtRTOID & " AS RTOID, " & txtNonConTypeID & " AS txtNonConTypeID," & _
                   "CodeName, Code, ColName, CodeObservation, AuditSortSequence, CodeNonCon,  #" & Format(InAuditDate, "dd-mmm-yyyy") & "# " & _
                   "FROM tblStandardPhrase  WHERE CodeName = 'AQTF" & CStr(i) & "'"
            db.Execute sSql
        End If
    Next

However, in this format AQTF1.1 it does not get picked up. I think that it is here where it fall over.

CODE

"FROM tblStandardPhrase WHERE CodeName = 'AQTF" & CStr(i) & "'"

I am not sure how to get the code to look at the 1.1

Many thanks

Doug Steele

Sep 21 2008, 05:51 AM

Easiest way would be

"FROM tblStandardPhrase WHERE CodeName = 'AQTF1." & CStr(i) & "'"

kapeller

Sep 21 2008, 06:18 AM

Hi Doug

Thanks for that advice. I had already tried that and it works.

However, the type of data ranges from AQTF1.1, AQTF2.1, AQTF3.2, AQTF4 and AQTF6

This is where I am having the problem?

Doug Steele

Sep 21 2008, 06:32 AM

So what are the values of the checkboxes? Surely you don't have ChkTRC1.1, ChkTRC2.1 etc.

kapeller

Sep 21 2008, 07:01 AM

Hi Doug

The value of the checkboxes are chkTRC1 chkTRC2 and so on

Hope this makes sense

Alan_G

Sep 21 2008, 07:46 AM

Hi Lou

If we're still talking about the same procedure as per this thread , then I think your problem will lie with

Dim i As Long

Try changing that to either

Dim i As Single or Dim i As Double

kapeller

Sep 21 2008, 07:50 AM

Hi Doug

Yes it is. It is just the type of data being entered and unfortunately it has to be with the dot eg AQTF1.1 and so on. It comes out of the "AQTF 2007 Essential Standards"

I will give what you have advised a try.

CyberCow

Sep 21 2008, 07:54 AM

You may need to run a nested loop to go through the elements on both sides of the decimal point in your control names.

Alan_G

Sep 21 2008, 07:59 AM

I've had a name change. I don't normally do that until each January 31st for security reasons - Social Security reasons

Doug Steele

Sep 21 2008, 08:09 AM

Easiest way would be to have a column in tblStandardPhrase that indicates the relationships between the CodeName values and the checkbox names. Let's assume that field is called SeqNo:

CODE

Dim rsPhrases As DAO.Recordset

  Set rsPhrases = db.OpenRecordset("SELECT CodeName, SeqNo FROM tblStandardPhrase")
  Do While rsPhrases.EOF = False
    If Me.Controls(sInitChar & "ChkTRC" & CStr(rsPhrases!SeqNo)) = -1 Then
    'Insert only if the value Yes CodeNonCon CI,
      sSql = "INSERT INTO tblAuditNonConformance (ANCAuditID, ANCRTOID,  ANCNonConTypeID, ANCAuditItem," & _
        "ANCAuditNonConformance, ANCAuDitType, ANCObservation, ANCAuditSortSequence, ANCCorrectiveAction,  ANCRecAuditDate) " & _
        "SELECT " & txtIDInAud & " AS IDInAud, " & txtRTOID & " AS RTOID, " & txtNonConTypeID & " AS txtNonConTypeID," & _
         "CodeName, Code, ColName, CodeObservation, AuditSortSequence, CodeNonCon,  #" & Format(InAuditDate, "dd-mmm-yyyy") & "# " & _
        "FROM tblStandardPhrase  WHERE CodeName = '" & rsPhrases!CodeName & "'"
      db.Execute sSql
    End If
    rsPhrases.MoveNext
  Loop

kapeller

Sep 22 2008, 03:46 AM

Hi Doug

Thank you very much for the above effort. I am currently trying to run the code and it stops at this point.

CODE

Set rsPhrases = db.OpenRecordset("SELECT CodeName, SeqNo FROM tblStandardPhrase")

I cannot get it to step into the rest of the code. I have made sure that my reflects yours as above.

Doug Steele

Sep 22 2008, 05:52 AM

What's the error you get? Did you put that code after wherever you've instantiated db?

kapeller

Sep 22 2008, 07:45 AM

Hi Doug

ther was no actual error message just it stoped at that point and moved onto an another callled routine at the original entry point.

This particular routine is called when I click on a button that then runs code.

Below is the entire routine you helped with. I have remarked out my bit of code and replaced your bit.

CODE

Private Sub subPopulateFieldAuditNonConCI()
    Dim sSql As String
    Dim db As DAO.Database
    Dim bPopulated As Boolean
    Dim i As Long, lStart As Long, lEnd As Long
    Dim sInitChar As String
    Dim rsPhrases As DAO.Recordset

    'Check if populated
    If DCount("AuditNonConID", "tblAuditNonConformance", "ANCNonConTypeID = 2 AND ANCAuditID = " & txtIDInAud) > 0 Then
        bPopulated = True
    Else
        bPopulated = False
    End If

    If bPopulated Then Exit Sub

    'Insert New Records
'    Set db = CurrentDb()
'    lStart = 1
'    lEnd = 7

  Set rsPhrases = db.OpenRecordset("SELECT CodeName, SeqNo FROM tblStandardPhrase")
  Do While rsPhrases.EOF = False
    If Me.Controls(sInitChar & "ChkTRC" & CStr(rsPhrases!SeqNo)) = -1 Then
    '''Insert only if the value Yes CodeNonCon CI,
      sSql = "INSERT INTO tblAuditNonConformance (ANCAuditID, ANCRTOID,  ANCNonConTypeID, ANCAuditItem," & _
        "ANCAuditNonConformance, ANCAuDitType, ANCObservation, ANCAuditSortSequence, ANCCorrectiveAction,  ANCRecAuditDate) " & _
        "SELECT " & txtIDInAud & " AS IDInAud, " & txtRTOID & " AS RTOID, " & txtNonConTypeID & " AS txtNonConTypeID," & _
         "CodeName, Code, ColName, CodeObservation, AuditSortSequence, CodeNonCon,  #" & Format(InAuditDate, "dd-mmm-yyyy") & "# " & _
        "FROM tblStandardPhrase  WHERE CodeName = '" & rsPhrases!CodeName & "'"
      db.Execute sSql
    End If
    rsPhrases.MoveNext
  Loop

'    For i = lStart To lEnd
'        If Me.Controls(sInitChar & "ChkTRC" & CStr(i)) = -1 Then    'Insert only if the value Yes CodeNonCon CI,
'            sSql = "INSERT INTO tblAuditNonConformance (ANCAuditID, ANCRTOID,  ANCNonConTypeID, ANCAuditItem," & _
'                   "ANCAuditNonConformance, ANCAuDitType, ANCObservation, ANCAuditSortSequence, ANCCorrectiveAction,  ANCRecAuditDate) " & _
'                   "SELECT " & txtIDInAud & " AS IDInAud, " & txtRTOID & " AS RTOID, " & txtNonConTypeID & " AS txtNonConTypeID," & _
'                   "CodeName, Code, ColName, CodeObservation, AuditSortSequence, CodeNonCon,  #" & Format(InAuditDate, "dd-mmm-yyyy") & "# " & _
'                   "FROM tblStandardPhrase  WHERE CodeName = 'AQTF" & CStr(i) & " '"
'            db.Execute sSql
'        End If
'    Next

End Sub

Many thanks

Doug Steele

Sep 22 2008, 08:55 AM

You've commented out the code that instantiates db:

' Set db = CurrentDb()

kapeller

Sep 22 2008, 03:01 PM

Hi Doug

I am very sorry for mucking you around. I do appreciate your help.

I have removed the comment from

Set db = CurrentDb()

The error message is

Too fe parameters. Expected 1

at this point when I try to step over this line of code

CODE

Set rsPhrases = db.OpenRecordset("SELECT CodeName, SeqNo FROM tblStandardPhrase")

Many thanks

Doug Steele

Sep 22 2008, 03:43 PM

I'm assuming you have fields named CodeName and SeqNo in your tblStandardPhrase table. CodeName, of course, is the name of the field you've already described. SeqNo is the field I suggested you add that mapped each CodeName to a specific checkbox on your form.

kapeller

Sep 22 2008, 03:51 PM

Hi Doug

Thanks for that feedback. I am about to leave for work so I will get back to it later this afternoon. Till then.........

kapeller

Sep 23 2008, 03:43 AM

Hi Doug

Once again thanks for your assistance.

I have been working incorporating your ideas and I must say I believe that I have got it right. However I do get a funny result.

In my table tblStandardPhrase have 3 x AQTF1.1 all OK

when I run your code, which works, I get in the tblAuditNonConformance 15 x AQTF1.1 it seems to multiply the result by 5 ????

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.