Solved

Correct Syntax for SQL statement

Posted on 2011-02-17
17
1,078 Views
Last Modified: 2012-06-27
I am writing some code in VBA under MS Dynamics GP to grab and look data in my table if it exists. the code currently works but I would like to modify it a little to incorporate something new in my statemtent. so here is the code and then I will tell you what I would like to do.

'grab the Work Order # or RTV # from table SVC05210
    strsql = " Select count(*) from SVC05210 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' "
    Set rst = cn.Execute(strsql)
    If rst.Fields(0).Value = 1 Then
        strsql = " select SVC_Document_Number from SVC05210 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' "
        Set rst = cn.Execute(strsql)
        StrWORTVNum.Value = Trim(rst.Fields("SVC_Document_Number").Value)
  end if

so here is a brief explanation of what it does. it looks in my table to see if the record exists and if it exists it run the second statment again to grab the correct field (SVC_Document_number ) and display it on my screen.


1- I would like to change the first statement to check if the field that I pulling is not empty as well.
something like: count all the record for RETDOCID = the one I am giving you , for the LINESEQNBR that I am giving you and that SVC_Document_number is not empty
below I have a snapshot of my table in SQL with the column showing the empty data , it currenlty shows (...) for empty field .

2-my second request would like to incorporate my 2 sql statement in one statment if possible instead of looking if it exists and then rerun the statement to pull the data

thank you.
David
 
0
Comment
Question by:taverny
  • 9
  • 5
  • 2
  • +1
17 Comments
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 34918022
I didn't get your first point.

Regarding the second point, use this:   // It's mentioned here http://www.experts-exchange.com/Microsoft/Applications/Microsoft_Dynamics/Q_26785792.html

Set CON = UserInfoGet.CreateADOConnection
    CON.CursorLocation = adUseClient
    CON.CommandTimeout = 1000
    CON.DefaultDatabase = UserInfoGet.IntercompanyID
    RST.LockType = adLockOptimistic
    RST.CursorType = adOpenKeyset
   
    RST.Open "select SVC_Document_Number from SVC05210 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' ", CON, , , adCmdText
    If RST.RecordCount > 0 Then
        StrWORTVNum.Value = Trim(rst.Fields("SVC_Document_Number").Value)
    End If
   
    RST.Close
    CON.Close

0
 
LVL 11

Expert Comment

by:Deepak Lakkad
ID: 34918027
Hi

Write code as described below:

strSql = 'your second sql statement'

set rst =  cn.Execute(strsql)

If rst.recordcount > 0 Then
        StrWORTVNum.Value = Trim(rst.Fields("SVC_Document_Number").Value)
end if


I think this will work.

My asumption is:

rst --> is a ADODB.Recordset Object

- Deepak Lakkad



0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34918152
Assuming that by 'empty' you mean that the column contains null, please try the following:

'grab the Work Order # or RTV # from table SVC05210
    strsql = " Select count(*) from SVC05210 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' and SVC_Document_number is not null "
    Set rst = cn.Execute(strsql)
    If rst.Fields(0).Value = 1 Then
        strsql = " select SVC_Document_Number from SVC05210 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' and SVC_Document_number is not null "
        Set rst = cn.Execute(strsql)
        StrWORTVNum.Value = Trim(rst.Fields("SVC_Document_Number").Value)
  end if

Open in new window


Please let me know what's the 'rst' variable type, so I can consolidate in a single statement.

I hope this helps.
0
 

Author Comment

by:taverny
ID: 34918166
Thank you guys,
my first point is to find the record as follow ( I am gonna try to put in the sql put my code will probably wrong)

 Select count(*) from SVC05210 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and  LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000'  and  SVC_Document_number  != "" "
0
 

Author Comment

by:taverny
ID: 34918191
I just saw your post I will try with what you told me:
my rst is an ado:

Dim rst As New ADODB.Recordset

thanks
0
 
LVL 10

Assisted Solution

by:Abdulmalek_Hamsho
Abdulmalek_Hamsho earned 200 total points
ID: 34918202
Ok then as wdosanjos, your code must look like this:

   Set CON = UserInfoGet.CreateADOConnection
    CON.CursorLocation = adUseClient
    CON.CommandTimeout = 1000
    CON.DefaultDatabase = UserInfoGet.IntercompanyID
    RST.LockType = adLockOptimistic
    RST.CursorType = adOpenKeyset
   
    RST.Open "select SVC_Document_Number from SVC05210 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' AND SVC_Document_Number IS NOT NULL", CON, , , adCmdText
    If RST.RecordCount > 0 Then
        StrWORTVNum.Value = Trim(rst.Fields("SVC_Document_Number").Value)
    End If
   
    RST.Close
    CON.Close

There might be some typos in my code as usual.
0
 

Author Comment

by:taverny
ID: 34918314
Wdosanjans , I replace my statement and it doesn't work it still runs like if the field has something . and I just realized I forgot to attached the snapshot of my table so here it is.

Abdulmalek Hamsho, I see the same code as Wdosanjos but with CON, ,, at the end of the statment. can you explain me a little more about what this do compare to what I am doing currently . I am gonna put my full code so you can see the whole process.

Thanks for your help

the procedure that we are currently working on is the :
Private Sub Grid_BeforeLinePopulate(RejectLine As Boolean)




--------------------------------------------------------------------------


ption Explicit

'This is for Database Connection
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim strsql As String

'This dim is for storing the lineSeqNumber since it keeps changing for no reason
Dim LineSequence As String
'this dim is to disable to run the update code for the field when data is populated
Dim IntRunCode As Integer

'This is to run Dexterity Code
Dim CompilerApp As Object
Dim CompilerMessage As String
Dim CompilerError As Integer
Dim Commands As String
   
'*********
'this Function is used to launch the Shell Execute for the report Quote
'*********
Private Declare Function ShellExecute _
    Lib "shell32.dll" _
    Alias "ShellExecuteA" ( _
    ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) _
As Long

'*********
' this code is for the lookup for RTV and WO
'*********
Private Sub ButWoRTVLookup_Changed()

    If Left(StrWORTVNum.Value, 3) = "WRK" Then
        WorkOrderEntryUpdate.Open
        WorkOrderEntryUpdate.WorkOrderNumber = StrWORTVNum.Value
    End If
   
    If Left(StrWORTVNum.Value, 3) = "RTV" Then
        RTVEntryUpdate.Open
        RTVEntryUpdate.RTVNumber = StrWORTVNum.Value
    End If
   
End Sub

'Private Declare Sub keybd_event Lib "User32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

'Public Sub KeyDown(ByVal vKey As Variant)
'keybd_event vKey, 0, &H1, 0
'End Sub
'*********
'this code go in item Maintenance and grab the Short description and Generic Description for the Item being repaired
'it also goes into User-Defined field and grab the Cust part# and requisition #
'*********
Private Sub Grid_BeforeLinePopulate(RejectLine As Boolean)
XReturn = 1
    'This Variable store the Line Item Sequence Becasue we can't do it on the fly
    LineSequence = LineSEQNumber.Value
    'this variable disable to run the changed field while populating the data
    IntRunCode = 0
    StrLine = CStr(CDbl(LineSequence)) / 100
    'Retrieve an ADO Connection for the current user
    'Set the current Database company
    Set cn = UserInfoGet.CreateADOConnection
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
   
    'Check in the database if the record exists in the database
    'strsql = " Select count(*) from PSHRMAXTRA where RMANUM = '" & RMAEntryUpdate.RMANumber & "'  and LINESQNUM = '" & Right(LineSequence, 6) & "000' "
    strsql = " Select count(*) from PSHRMAXTRA where RMANUM = '" & RMAEntryUpdate.RMANumber & "'  and LINESQNUM = '" & CStr(CDbl(LineSequence)) & ".00000' "
    Set rst = cn.Execute(strsql)
   
    'if the record exists pull the rush field
    If rst.Fields(0).Value = 1 Then
        strsql = " select * from PSHRMAXTRA where RMANUM = '" & RMAEntryUpdate.RMANumber & "' and LINESQNUM = '" & CStr(CDbl(LineSequence)) & ".00000' "
        Set rst = cn.Execute(strsql)
        If rst.Fields("RUSH").Value = True Then
            Rush.Value = 1
        Else
            Rush.Value = 0
        End If
    End If

    'grab the Generic Description and Short Description for the item number
    strsql = " Select count(*) from IV00101 where ITEMNMBR = '" & RMAEntryUpdateDetail.ReturnItemNumber & "' "
    Set rst = cn.Execute(strsql)
    If rst.Fields(0).Value = 1 Then
        strsql = " select * from IV00101 where ITEMNMBR = '" & RMAEntryUpdateDetail.ReturnItemNumber & "' "
        Set rst = cn.Execute(strsql)
        StrManufac = Trim(rst.Fields("ITMSHNAM").Value)
        StrTypeOfProd = Trim(rst.Fields("ITMGEDSC").Value)
    End If
   
    'grab the Cust part# from table SVC05200
    strsql = " Select count(*) from SVC05200 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' "
    Set rst = cn.Execute(strsql)
    If rst.Fields(0).Value = 1 Then
        strsql = " select USRDEF03 from SVC05200 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' "
        Set rst = cn.Execute(strsql)
        StrCustPart.Value = Trim(rst.Fields("USRDEF03").Value)
    End If
   
    'grab the Work Order # or RTV # from table SVC05210
    strsql = " Select count(*) from SVC05210 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' and SVC_Document_Number is not Null"
    Set rst = cn.Execute(strsql)
    If rst.Fields(0).Value = 1 Then
        strsql = " select SVC_Document_Number from SVC05210 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' "
        Set rst = cn.Execute(strsql)
        StrWORTVNum.Value = Trim(rst.Fields("SVC_Document_Number").Value)
        RepairItInHouse.Visible = False
        Subcontract.Visible = False
    Else
        RepairItInHouse.Visible = True
        Subcontract.Visible = True
       
    End If
   
   
       'If line Item doesn't exists yet don't execute the code
      If LineSequence <> "$0.00" Then
       
        'backup the Original Noteindex to a dummie string (Backup)
        StrBackupNote = NoteIndex.Value
       
        'Get the new IndexNote number from the WorkOrder Tables
        'this count in the open table WorkOrder to see if the record exists
        strsql = " Select count(*) from SVC06100 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' "
        Set rst = cn.Execute(strsql)
       
        'if the note exist in the WORK run the SQL and Save the new Noteindex to the good NoteIndex for an RMA that is not Historical
        If rst.Fields(0).Value = 1 Then
            strsql = " select NOTEINDX from SVC06100 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' "
            Set rst = cn.Execute(strsql)
            NoteIndex = rst.Fields("NOTEINDX").Value
           
            'check in the table of notes to see if a note actually has content
            strsql = " Select count(*) from SY03900 where NOTEINDX = '" & Right(NoteIndex, Len(NoteIndex) - 1) & "000'"
            Set rst = cn.Execute(strsql)
            'if content exists higlished the yellow one and disable the other one , if not enable the other one
            If rst.Fields(0).Value = 1 Then
                PBDepotNotePresent.Visible = True
                PBDepotNoteEmpty.Visible = False
            Else
                PBDepotNoteEmpty.Visible = True
                PBDepotNotePresent.Visible = False
            End If
            'Restore the original Index to not modify original code
            NoteIndex = StrBackupNote.Value
        End If
     End If
     
   
   
    'This pass the info to the Sub to reject ??? and the intRuncCode is set back to 1 so any changes is covered
    RejectLine = False
    IntRunCode = 1
   
End Sub


'*********
' This sub is to pull the Note associate to the RMA from The WorkOrder, this is the external note for Client , on a note empty
'*********
Private Sub PBDepotNoteEmpty_Changed()

    'if the line item is not 00
    If LineSEQNumber <> "$0.00" Then
        'we backup the original value of the index
        StrBackupNote = NoteIndex.Value
        Set cn = UserInfoGet.CreateADOConnection
        cn.DefaultDatabase = UserInfoGet.IntercompanyID
        cmd.ActiveConnection = cn
        'we search the index in the table to see if record as data
        strsql = " Select count(*) from SVC06100 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' "
        Set rst = cn.Execute(strsql)
       
        'if record exist then fill the field with the note index from RMA else check the note in the historical database
        If rst.Fields(0).Value = 1 Then
            strsql = " select NOTEINDX from SVC06100 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' "
            Set rst = cn.Execute(strsql)
            NoteIndex = rst.Fields("NOTEINDX").Value
        End If
        'open the note by calling the original note button
        RMAEntryUpdate.ItemNotePresent.Value = 1
        'NoteArrayPresentWindowArea.Value = 1
        'put back the original index to leave the code intact
        NoteIndex = StrBackupNote.Value
     End If
End Sub


'*********
' This sub is to pull the Note associate to the RMA from The WorkOrder, this is the external note for Client , on a note empty
'*********
Private Sub PBDepotNotePresent_Changed()

    'if the line item is not 00
    If LineSEQNumber <> "$0.00" Then
        'we backup the original value of the index
        StrBackupNote = NoteIndex.Value
        Set cn = UserInfoGet.CreateADOConnection
        cn.DefaultDatabase = UserInfoGet.IntercompanyID
        cmd.ActiveConnection = cn
        'we search the index in the table to see if record as data
        strsql = " Select count(*) from SVC06100 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' "
        Set rst = cn.Execute(strsql)
       
        'if record exist then fill the field with the note index from RMA else check the note in the historical database
        If rst.Fields(0).Value = 1 Then
            strsql = " select NOTEINDX from SVC06100 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' "
            Set rst = cn.Execute(strsql)
            NoteIndex = rst.Fields("NOTEINDX").Value
        End If
        'open the note by calling the original note button
        RMAEntryUpdate.ItemNotePresent.Value = 1
        'NoteArrayPresentWindowArea.Value = 1
        'put back the original index to leave the code intact
        NoteIndex = StrBackupNote.Value
     End If
End Sub








Private Sub RepairItInHouse_AfterUserChanged()
    XReturn = 5
   ' Process = 1
    'RMALineProcessReturn.hide
    'KeyDown (&H9)
  '  SendKeys "{TAB}"
 '   SendKeys "+{TAB}"
    'KeyDown (&H69)
    'RMALineProcessReturnDetail.SVCProcessType.Focus (4)
'   RMALineProcessReturn.Create = 1

'KeyDown (&H26)
End Sub



Private Sub ReturnItemNumber_AfterUserChanged()



'Retrieve an ADO Connection for the current user
    'Set the current Database company
    Set cn = UserInfoGet.CreateADOConnection
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
    cmd.ActiveConnection = cn
        'grab the Generic Description and Short Description for the item number
    strsql = " Select count(*) from IV00101 where ITEMNMBR = '" & RMAEntryUpdateDetail.ReturnItemNumber & "' "
    Set rst = cn.Execute(strsql)
    If rst.Fields(0).Value = 1 Then
        strsql = " select * from IV00101 where ITEMNMBR = '" & RMAEntryUpdateDetail.ReturnItemNumber & "' "
        Set rst = cn.Execute(strsql)
        StrManufac = Trim(rst.Fields("ITMSHNAM").Value)
        StrTypeOfProd = Trim(rst.Fields("ITMGEDSC").Value)
    End If
   
End Sub



'*********
'update the Rush Field CheckBox
'*********
Private Sub Rush_Changed()
   
    If IntRunCode = 1 Then
        'Retrieve an ADO Connection for the current user
        'Set the current Database company
        Set cn = UserInfoGet.CreateADOConnection
        cn.DefaultDatabase = UserInfoGet.IntercompanyID
        cmd.ActiveConnection = cn
   
        'Check in the database if the record exists in the database
        strsql = " Select count(*) from PSHRMAXTRA where RMANUM = '" & RMAEntryUpdate.RMANumber & "' and LINESQNUM = '" & CStr(CDbl(LineSequence)) & ".00000' "
        Set rst = cn.Execute(strsql)
       
        'if the record already exists we do an update otherwise we do an insert
        If rst.Fields(0).Value = 1 Then
            cmd.CommandText = " UPDATE PSHRMAXTRA set RUSH = '" & Rush & "' WHERE RMANUM = '" & RMAEntryUpdate.RMANumber & "' and LINESQNUM = '" & CStr(CDbl(LineSequence)) & ".00000' "
            cmd.Execute
        Else
            cmd.CommandText = "insert INTO PSHRMAXTRA (RMANUM, RUSH, LINESQNUM) VALUES ('" & RMAEntryUpdate.RMANumber & "', '" & Rush & "', '" & CStr(CDbl(LineSequence)) & ".00000' )"
            cmd.Execute
        End If
       
        ' Create link without having reference marked
        Set CompilerApp = CreateObject("Dynamics.Application")
        Commands = "local boolean Result;" & vbCrLf
        If Rush.Value = 1 Then
            Commands = Commands & "Result = Field_SetFontColor('Return Document ID' of window SVC_RMA_Entry_DropDown of form SVC_RMA_Update, COLOR_RED);" & vbCrLf
        Else
            Commands = Commands & "Result = Field_SetFontColor('Return Document ID' of window SVC_RMA_Entry_DropDown of form SVC_RMA_Update, COLOR_BLACK);" & vbCrLf
        End If
        CompilerApp.CurrentProductID = 949 ' Field Services
        CompilerApp.CurrentProduct = CompilerApp.CurrentProduct & "!Modified"
        CompilerError = CompilerApp.ExecuteSanscript(Commands, CompilerMessage)
        If CompilerError <> 0 Then
            MsgBox CompilerMessage
        End If
       
    End If
   
End Sub

'*********
'update the UserDefined window for Customer Part
'*********
Private Sub StrCustPart_Changed()
   
    If IntRunCode = 1 Then
        'Retrieve an ADO Connection for the current user
        'Set the current Database company
        Set cn = UserInfoGet.CreateADOConnection
        cn.DefaultDatabase = UserInfoGet.IntercompanyID
        cmd.ActiveConnection = cn
               
        'push the cust part# in table SVC05200
        strsql = " Select count(*) from SVC05200 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' "
        Set rst = cn.Execute(strsql)
        If rst.Fields(0).Value = 1 Then
            cmd.CommandText = " UPDATE SVC05200 set USRDEF03='" & StrCustPart & "' where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' "
            cmd.Execute
        Else
            MsgBox ("The line Item is not created yet. You need to Create the line item then retype the Customer Part#. If you don't retype it, the Customer Part will not be saved")
            StrCustPart.Value = ""
        End If
    End If

End Sub

'*********
' Code to print Quote with No Rush Fee
'*********
Private Sub PrintQuote_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
   
    Dim res As Long
    Dim URL As String
    ' it is mandatory that the URL is prefixed with http:// or https://
    URL = "http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fRMAQuote9_NoRush&rs:Command=Render&RMANumbr=" & RMAEntryUpdate.RMANumber
    res = ShellExecute(0, "open", URL, 0, 0, 1)

End Sub

'*********
' Code to print Quote with Rush Fee
'*********
Private Sub PrintQuoteWithRush_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
   
    Dim res As Long
    Dim URL As String
    ' it is mandatory that the URL is prefixed with http:// or https://
    URL = "http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fRMAQuote9&rs:Command=Render&RMANumbr=" & RMAEntryUpdate.RMANumber
    res = ShellExecute(0, "open", URL, 0, 0, 1)

End Sub


tabledsad.JPG
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34918439
It's hard to tell from the screenshot what value represents empty content.  I guess it's all spaces.  If that's the case, try changing the statement as follows:

"select count(*) from SVC05210 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' AND SVC_Document_Number <> ' ' "

"select SVC_Document_Number from SVC05210 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' AND SVC_Document_Number <> ' ' "

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:taverny
ID: 34918490
ok , I am gonna try it now. I was not pointing to the one highlited , they all look like if there is 3 dots (...) at the end of the field.
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34918526
The 3 dots indicate that the value is not completely displayed.  You need to expand the column until the (...) disappear to see the complete value.
0
 

Author Comment

by:taverny
ID: 34918551
ok it does work. AND SVC_Document_Number <> ' ' took care of it.
I am reposting the snapshot of the screen since the 3 dots where everywhere and realized that I could expand it.
so in case my field has one blank or 20 blank or space I don't know how you call it. this code will work right?
thanks

tabledsad.JPG
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34918628
Yes, regardless of the number of spaces the code will work.  I was not sure if the (...) were hiding a value to the right, but that's not the case.

As Abdulmalek_Hamsho indicated, you can consolidate the two statements as follows:
'grab the Work Order # or RTV # from table SVC05210
    strsql = "select SVC_Document_Number from SVC05210 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' AND SVC_Document_Number <> ' ' "

    Set rst = cn.Execute(strsql)
    If rst.RecordCount = 1 Then
        StrWORTVNum.Value = Trim(rst.Fields("SVC_Document_Number").Value)
    end if

Open in new window

0
 

Author Comment

by:taverny
ID: 34918678
Thank you I will try it now to see if it works , that will cleanup my code a little.
0
 

Author Comment

by:taverny
ID: 34918758
it doesn't work. the rst.Recorcount return -1 , and I can see in the table that the record exists
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 300 total points
ID: 34918921
OK. It's possible that the provider does not support RecordCount, which seems to be the case here.  You can try changing the 'if' statement as follows:

'grab the Work Order # or RTV # from table SVC05210
    strsql = "select SVC_Document_Number from SVC05210 where RETDOCID = '" & RMAEntryUpdate.RMANumber & "' and LNSEQNBR = '" & CStr(CDbl(LineSequence)) & ".00000' AND SVC_Document_Number <> ' ' "

    Set rst = cn.Execute(strsql)
    If not rst.EOF Then
        StrWORTVNum.Value = Trim(rst.Fields("SVC_Document_Number").Value)
    end if

Open in new window

0
 

Author Comment

by:taverny
ID: 34919178
oK, it works now.
thanks
0
 

Author Closing Comment

by:taverny
ID: 34919300
Thank you guys for your help
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now