By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 917 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

JOIN expression not supported

P: 30
I have been working on making my DB DSN-less and I have a bit of a problem. After racking my brain all day over the ODBC connection string to an Informix database and how to link to the tables as recordsets, I have finally got it to work!

But then I tried to QUERY the recordsets....

Here is the SQL String I am using.

Expand|Select|Wrap|Line Numbers
  1.         strSQL = "INSERT INTO Logsheet_Table ( Ticket_No, Date_Entered, Time_Entered, Ticket_Date, Cust_Code, Cust_Name, Cust_Address, Cust_AddressA, Cust_Address_2, " & _
  2.                  "Cust_Address_2A, Cust_City, Cust_CityA, Cust_State, Cust_StateA, Cust_Zip_Code, Cust_Zip_CodeA, Ticket_Type, Status, Sort_Code ) " & _
  3.                  "SELECT "" & RsHdr.ioqh_nbr & "", Date() AS Date_Entered, Time() AS Time_Entered, "" & RsHdr.ioqh_dt & "", "" & RsHdr.ioqh_cust_cd & "", "" & RsHdr.ioqh_cust_nm & "", " & _
  4.                  """ & RsCustAddr.custa_frst_ln & "", "" & RsHdrAddr.ioqhe_frst_ln & "", "" & RsCustAddr.custa_scnd_ln & "", "" & RsHdrAddr.ioqhe_scnd_ln & "", "" & RsCustAddr.custa_city & "", " & _
  5.                  "Trim(["" & RsHdr.ioqhe_city & ""]) AS Cust_City, "" & RsCustAddr.custa_state & "", "" & RsHdrAddr.ioqhe_state & "", "" & RsCustAddr.custa_zip_cd & "", "" & RsHdrAddr.ioqhe_zip_cd & "", " & _
  6.                  """ & RsHdr.ioqh_type & "", ""OE"" AS Status, ""E"" AS Sort " & _
  7.                  "FROM ((("" & RsHdr & "" INNER JOIN "" & RsCust & "" ON "" & RsHdr.ioqh_cust_cd & ""="" & RsCust.cust_cd & "") LEFT JOIN "" & RsHdrAddr & "" ON "" & RsHdr.ioqh_id & ""="" & RsHdrAddr.ioqh_id & "") INNER JOIN "" & RsCustAddr & "" ON "" & RsCust.cust_id & ""="" & RsCustAddr.cust_id & "")" & _
  8.                  "WHERE ((("" & RsHdr.ioqh_nbr & "")=[Forms]![Frm_Logsheet_Today].[txtTicketNo]));"
  9.  
  10.  
And the ODBC connection string code
Expand|Select|Wrap|Line Numbers
  1. Dim Answer, strSQL, strConn As String
  2. Dim Conn1 As New ADODB.Connection
  3. Dim RsHdr, RsCust, RsCustAddr, RsHdrAddr, RsLookup As ADODB.Recordset
  4.  
  5. strConn = "ODBC;Dsn='';" & _
  6.           "Driver={INFORMIX 3.81 32 BIT};" & _
  7.           "Host=192.168.1.3;" & _
  8.           "Server=dataline_725;" & _
  9.           "Service=20000;" & _
  10.           "Protocol=sesoctcp;" & _
  11.           "Database=ecspro;" & _
  12.           "UID=odbc;" & _
  13.           "PWD=odbc"
  14.  
  15. Conn1.Open strConn
  16.  
  17. Set RsHdr = New ADODB.Recordset
  18. RsHdr.CursorLocation = adUseClient
  19. RsHdr.CursorType = adOpenKeyset
  20. Set RsHdr = Conn1.Execute("SELECT * FROM informix.ioq_hdr")
  21.  
  22. Set RsCust = New ADODB.Recordset
  23. RsCust.CursorLocation = adUseClient
  24. RsCust.CursorType = adOpenKeyset
  25. Set RsCust = Conn1.Execute("SELECT * FROM informix.cust")
  26.  
  27. Set RsHdrAddr = New ADODB.Recordset
  28. RsHdrAddr.CursorLocation = adUseClient
  29. RsHdrAddr.CursorType = adOpenKeyset
  30. Set RsHdrAddr = Conn1.Execute("SELECT * FROM informix.ioq_hdr_addr")
  31.  
  32. Set RsCustAddr = New ADODB.Recordset
  33. RsCustAddr.CursorLocation = adUseClient
  34. RsCustAddr.CursorType = adOpenKeyset
  35. Set RsCustAddr = Conn1.Execute("SELECT * FROM informix.cust_addr")
  36.  
  37. Set RsLookup = New ADODB.Recordset
  38. RsLookup.CursorLocation = adUseClient
  39. RsLookup.CursorType = adOpenKeyset
  40. Set RsLookup = Conn1.Execute("SELECT * FROM informix.ioq_hdr WHERE ioqh_nbr='" & TicketNo & "';")
My problem is on line #7 of the SQL statement. I get "Run-time error '3296'"
"JOIN expression not supported".

After a quick Google search, seems the JOIN statement may not be parenthesized correctly, but I cannot see where. Any help would be greatly appreciated!
Jul 25 '12 #1
Share this Question
Share on Google+
17 Replies


Rabbit
Expert Mod 10K+
P: 12,355
It would help to see the string that's actually submitted.
Jul 25 '12 #2

P: 30
Thank you for the quick response.

The SQL string is executed by an After Update event using
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL strSQL
Jul 25 '12 #3

Rabbit
Expert Mod 10K+
P: 12,355
I didn't mean how is it executed. I wanted to know what is in the variable right before that line of code.
Jul 26 '12 #4

P: 30
Here is the entire procedure. I apologize if I have not been clear.
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtTicketNo_AfterUpdate()
  2. On Error GoTo ERR_txtTicketNo_AfterUpdate
  3.  
  4. Dim Answer, strSQL, strConn As String
  5. Dim TicketNo As Long
  6. Dim Conn1 As New ADODB.Connection
  7. Dim RsHdr, RsCust, RsCustAddr, RsHdrAddr, RsLookup As ADODB.Recordset
  8.  
  9. strConn = "ODBC;Dsn='';" & _
  10.           "Driver={INFORMIX 3.81 32 BIT};" & _
  11.           "Host=192.168.1.3;" & _
  12.           "Server=dataline_725;" & _
  13.           "Service=20000;" & _
  14.           "Protocol=sesoctcp;" & _
  15.           "Database=ecspro;" & _
  16.           "UID=odbc;" & _
  17.           "PWD=odbc"
  18.  
  19. Conn1.Open strConn
  20.  
  21. Set RsHdr = New ADODB.Recordset
  22. RsHdr.CursorLocation = adUseClient
  23. RsHdr.CursorType = adOpenKeyset
  24. Set RsHdr = Conn1.Execute("SELECT * FROM informix.ioq_hdr")
  25.  
  26. Set RsCust = New ADODB.Recordset
  27. RsCust.CursorLocation = adUseClient
  28. RsCust.CursorType = adOpenKeyset
  29. Set RsCust = Conn1.Execute("SELECT * FROM informix.cust")
  30.  
  31. Set RsHdrAddr = New ADODB.Recordset
  32. RsHdrAddr.CursorLocation = adUseClient
  33. RsHdrAddr.CursorType = adOpenKeyset
  34. Set RsHdrAddr = Conn1.Execute("SELECT * FROM informix.ioq_hdr_addr")
  35.  
  36. Set RsCustAddr = New ADODB.Recordset
  37. RsCustAddr.CursorLocation = adUseClient
  38. RsCustAddr.CursorType = adOpenKeyset
  39. Set RsCustAddr = Conn1.Execute("SELECT * FROM informix.cust_addr")
  40.  
  41. TicketNo = Nz(Me.txtTicketNo)
  42.  
  43. Set RsLookup = New ADODB.Recordset
  44. RsLookup.CursorLocation = adUseClient
  45. RsLookup.CursorType = adOpenKeyset
  46. Set RsLookup = Conn1.Execute("SELECT * FROM informix.ioq_hdr WHERE ioqh_nbr='" & TicketNo & "';")
  47.  
  48. If RsLookup.BOF = True And RsLookup.EOF = True Then
  49.  
  50.     ' Record does not exist
  51.  
  52.     MsgBox "This is not a valid transaction number!", vbExclamation, "Invalid Transaction Number"
  53.  
  54.     Me.txtTicketNo = ""
  55.  
  56.     Me.txtTicketNo.SetFocus
  57.  
  58.     Exit Sub
  59.  
  60. End If
  61.  
  62. If IsNull(DLookup("Log_ID", "Logsheet_Table", "[Ticket_No]= " & TicketNo & "")) Then
  63.  
  64.    ' Record does not exist
  65.  
  66.         strSQL = "INSERT INTO Logsheet_Table ( Ticket_No, Date_Entered, Time_Entered, Ticket_Date, Cust_Code, Cust_Name, Cust_Address, Cust_AddressA, Cust_Address_2, " & _
  67.                  "Cust_Address_2A, Cust_City, Cust_CityA, Cust_State, Cust_StateA, Cust_Zip_Code, Cust_Zip_CodeA, Ticket_Type, Status, Sort_Code ) " & _
  68.                  "SELECT "" & RsHdr.ioqh_nbr & "", Date() AS Date_Entered, Time() AS Time_Entered, "" & RsHdr.ioqh_dt & "", "" & RsHdr.ioqh_cust_cd & "", "" & RsHdr.ioqh_cust_nm & "", " & _
  69.                  """ & RsCustAddr.custa_frst_ln & "", "" & RsHdrAddr.ioqhe_frst_ln & "", "" & RsCustAddr.custa_scnd_ln & "", "" & RsHdrAddr.ioqhe_scnd_ln & "", "" & RsCustAddr.custa_city & "", " & _
  70.                  "Trim(["" & RsHdr.ioqhe_city & ""]) AS Cust_City, "" & RsCustAddr.custa_state & "", "" & RsHdrAddr.ioqhe_state & "", "" & RsCustAddr.custa_zip_cd & "", "" & RsHdrAddr.ioqhe_zip_cd & "", " & _
  71.                  """ & RsHdr.ioqh_type & "", ""OE"" AS Status, ""E"" AS Sort " & _
  72.                  "FROM ((("" & RsHdr & "" INNER JOIN "" & RsCust & "" ON "" & RsHdr.ioqh_cust_cd & ""="" & RsCust.cust_cd & "") LEFT JOIN "" & RsHdrAddr & "" ON "" & RsHdr.ioqh_id & ""="" & RsHdrAddr.ioqh_id & "") INNER JOIN "" & RsCustAddr & "" ON "" & RsCust.cust_id & ""="" & RsCustAddr.cust_id & "")" & _
  73.                  "WHERE ((("" & RsHdr.ioqh_nbr & "")=[Forms]![Frm_Logsheet_Today].[txtTicketNo]));"
  74.  
  75.     DoCmd.RunSQL strSQL
  76.  
  77.     Me.txtTicketNo = ""
  78.  
  79.     Form.Refresh
  80.  
  81.     Me.txtTicketNo.SetFocus
  82.  
  83.     Exit Sub
  84.  
  85. Else
  86.  
  87.     Answer = MsgBox("This ticket is already on today's logsheet!" & vbNewLine & "Would you like to add it anyway?", vbYesNo, "Duplicate Ticket No found")
  88.  
  89.     If Answer = vbYes Then
  90.  
  91.         stDocName = "Qry_Get_Ticket"
  92.         DoCmd.OpenQuery stDocName
  93.  
  94.         Me.txtTicketNo = ""
  95.  
  96.         Form.Refresh
  97.  
  98.         Me.txtTicketNo.SetFocus
  99.  
  100.         Exit Sub
  101.  
  102.     Else
  103.  
  104.         Me.txtTicketNo = ""
  105.  
  106.         Form.Refresh
  107.  
  108.         Me.txtTicketNo.SetFocus
  109.  
  110.         Exit Sub
  111.  
  112.     End If
  113.  
  114. End If
  115.  
  116. Exit_txtTicketNo_AfterUpdate:
  117.  
  118. RsHdr.Close
  119. RsCust.Close
  120. RsHdrAddr.Close
  121. RsCustAddr.Close
  122. RsLookup.Close
  123.  
  124. Conn1.Close
  125.  
  126. Set RsHdr = Nothing
  127. Set RsCust = Nothing
  128. Set RsHdrAddr = Nothing
  129. Set custhdr = Nothing
  130. Set RsLookup = Nothing
  131.  
  132. Set Conn1 = Nothing
  133.  
  134.     Exit Sub
  135.  
  136. ERR_txtTicketNo_AfterUpdate:
  137.     MsgBox Err.Description
  138.     Resume Exit_txtTicketNo_AfterUpdate
  139.  
  140. End Sub
  141.  
The Run-Time Error highlights line #75 on debug and states JOIN expression not supported.
Jul 26 '12 #5

dsatino
100+
P: 393
SQL syntax is not the same across all database types. You need to check on the syntax that is used for the specific type of database that you are passing the SQL string to. In your case it tells you that the 'JOIN expression is not supported'. So start with that.
Jul 27 '12 #6

Rabbit
Expert Mod 10K+
P: 12,355
@Wesley, that's not what I meant either. You already posted your code. I only need to know the actual value of the variable that is being submitted to the DBMS.

Take this code for example:
Expand|Select|Wrap|Line Numbers
  1. intValue = 5
  2. sqlString = "SELECT *" & vbCrLf & "FROM someTable" & vbCrLf & "WHERE ID = " & intValue
When I say I want to see what's in the variable. I want to see this:
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM someTable
  3. WHERE ID = 5
And I don't mean what you think is in the variable or what you expect to be in the variable. I want to know the actual variable value as it exists in your computer's memory.
Jul 27 '12 #7

dsatino
100+
P: 393
Actually, I think the first thing you should do is examine your use of quotes in building your strSQL variable.
Jul 27 '12 #8

Rabbit
Expert Mod 10K+
P: 12,355
@dsatino, that's why I want to see what's in his variable. It will reveal any misquotations along with other SQL syntax errors.
Jul 27 '12 #9

dsatino
100+
P: 393
Well, that's easy. He gets this:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Logsheet_Table ( Ticket_No, Date_Entered, Time_Entered, Ticket_Date, Cust_Code, Cust_Name, Cust_Address, Cust_AddressA, Cust_Address_2, Cust_Address_2A, Cust_City, Cust_CityA, Cust_State, Cust_StateA, Cust_Zip_Code, Cust_Zip_CodeA, Ticket_Type, Status, Sort_Code ) SELECT " & RsHdr.ioqh_nbr & ", Date() AS Date_Entered, Time() AS Time_Entered, " & RsHdr.ioqh_dt & ", " & RsHdr.ioqh_cust_cd & ", " & RsHdr.ioqh_cust_nm & ", " & RsCustAddr.custa_frst_ln & ", " & RsHdrAddr.ioqhe_frst_ln & ", " & RsCustAddr.custa_scnd_ln & ", " & RsHdrAddr.ioqhe_scnd_ln & ", " & RsCustAddr.custa_city & ", Trim([" & RsHdr.ioqhe_city & "]) AS Cust_City, " & RsCustAddr.custa_state & ", " & RsHdrAddr.ioqhe_state & ", " & RsCustAddr.custa_zip_cd & ", " & RsHdrAddr.ioqhe_zip_cd & ", " & RsHdr.ioqh_type & ", "OE" AS Status, "E" AS Sort FROM (((" & RsHdr & " INNER JOIN " & RsCust & " ON " & RsHdr.ioqh_cust_cd & "=" & RsCust.cust_cd & ") LEFT JOIN " & RsHdrAddr & " ON " & RsHdr.ioqh_id & "=" & RsHdrAddr.ioqh_id & ") INNER JOIN " 
  2. & RsCustAddr & " ON " & RsCust.cust_id & "=" & RsCustAddr.cust_id & ")WHERE (((" & RsHdr.ioqh_nbr & ")=[Forms]![Frm_Logsheet_Today].[txtTicketNo]));
Which is not a valid sql string in anyway of course, but I think it's better to make them work that out themselves by pointing them in the right direction.
Jul 27 '12 #10

P: 30
@Rabbit

Are you wanting to see what EACH variable is equal to or just the one in the WHERE statement?
Expand|Select|Wrap|Line Numbers
  1. WHERE (((" & RsHdr.ioqh_nbr & ")=726148));
@dsatino

I have always assumed that this was a syntax error probably related to quotations. This is the first time I have tried using a variable for a recordset in a SQL string so I thought there may be a chance that this is actually not supported, which is why I posed the question in the way that I did.

Either way, thank you both for your responses!
Jul 31 '12 #11

NeoPa
Expert Mod 15k+
P: 31,409
Wesley Hader:
Here is the SQL String I am using.
Actually, that's not a SQL string at all Wesley. It's some VBA code that creates a SQL string using some literal values, but also some other variable values that are not available to us (as you haven't shared this information). I can see that other experts have also stumbled into this problem in the thread.

Please read Before Posting (VBA or SQL) Code. This will help everyone to help you in a more timely manner.
Jul 31 '12 #12

dsatino
100+
P: 393
Ah, I see...I think.

I Wesley is trying to reference his recordset variables with his SQL statement. If that's the case, then no Wesley you can't do that. You can, however, use your recordsets to build a proper SQL string that you can run.
Jul 31 '12 #13

P: 30
Thank you dsatino! It seemed like a long shot when I first attempted it, but I thought I would give it a shot.

Can you point me in the right direction on using ADO recordsets in VBA, creating the VBA TEXT string, and using that to create the SQL string? My problem, I assume, will arise in trying to create the JOINs or what I am thinking of as the relationships between the recordsets.

I know my wording has not been up to par and that I did not provide enough information to make this an easy question to answer, but I appreciate the effort anyways!
Jul 31 '12 #14

dsatino
100+
P: 393
Before you go down that road...

Do you have your DB linked to these remote tables?
Jul 31 '12 #15

P: 30
That's how I got here....

I can connect to these tables using an ODBC System DSN, but this problem arose when trying to create the connections with VBA Connection strings and manually creating the ADO recordsets. My goal is to have everything coded and go away from using DSN's.
Aug 1 '12 #16

dsatino
100+
P: 393
Ok, well then there a couple of ways to go about this. Here's one:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtTicketNo_AfterUpdate() 
  2.     On Error GoTo ERR_txtTicketNo_AfterUpdate 
  3.  
  4.     Dim Answer, strSQL, strConn As String 
  5.     Dim TicketNo As Long 
  6.     Dim Conn1 As New ADODB.Connection 
  7.     Dim RsHdr, RsCust, RsCustAddr, RsHdrAddr, rsRemote, RsLookup As ADODB.Recordset 
  8.     dim db as database
  9.     dim rsLocal as recordset
  10.  
  11.     strConn = "ODBC;Dsn='';" & _ 
  12.               "Driver={INFORMIX 3.81 32 BIT};" & _ 
  13.               "Host=192.168.1.3;" & _ 
  14.               "Server=dataline_725;" & _ 
  15.               "Service=20000;" & _ 
  16.               "Protocol=sesoctcp;" & _ 
  17.               "Database=ecspro;" & _ 
  18.               "UID=odbc;" & _ 
  19.               "PWD=odbc" 
  20.  
  21.     Conn1.Open strConn 
  22.  
  23.     'TicketNo = Nz(Me.txtTicketNo) 
  24.     TicketNo = Nz(Me.txtTicketNo,-1)
  25.     'you should now test for TicketNo = -1 and exit the sub
  26.  
  27.     Set RsLookup = New ADODB.Recordset 
  28.     RsLookup.CursorLocation = adUseClient 
  29.     RsLookup.CursorType = adOpenKeyset 
  30.     Set RsLookup = Conn1.Execute("SELECT * FROM informix.ioq_hdr WHERE ioqh_nbr='" & TicketNo & "';")  'ticketno is in a text field here
  31.  
  32.     If RsLookup.BOF = True And RsLookup.EOF = True Then       
  33.         ' Record does not exist 
  34.         MsgBox "This is not a valid transaction number!", vbExclamation, "Invalid Transaction Number"
  35.         Me.txtTicketNo = "" 
  36.         Me.txtTicketNo.SetFocus 
  37.         Exit Sub 'You should explicitly close your connections and recordsets
  38.     End If 
  39.  
  40.     If IsNull(DLookup("Log_ID", "Logsheet_Table", "[Ticket_No]= " & TicketNo & "")) Then 'ticketno is in a numeric field here
  41.        ' Record does not exist 
  42.         set db=currentdb
  43.         set rsLocal=db.openrecordset("Logsheet_Table",dbopendynamic)
  44.         set rsRemote= Conn1.Execute("SELECT hdr.ioqhdt,hdr.ioqhcustcd,hdr.ioqhcustnm, " & _  
  45.              "cadd.custafrstln,hadd.ioqhefrstln,cadd.custascndln,hadd.ioqhescndln,cadd.custacity,hdr.ioqhecity AS CustCity,cadd.custastate, " & _
  46.              "hadd.ioqhestate, cadd.custazipcd,hadd.ioqhezipcd,hdr.ioqhtype,OE AS Status,E AS Sort " & _
  47.              "FROM (((informix.ioq_hdr hdr   INNER JOIN   informix.cust cust   ON   hdr.ioqhcustcd  =  cust.custcd) " & _
  48.              "LEFT JOIN informix.ioq_hdr_addr hadd ON hdr.ioqhid  =  hadd.ioqhid) " & _
  49.              "INNER JOIN   informix.cust_addr cadd   ON   cust.custid  =  cadd.custid) " & _
  50.              "WHERE hdr.ioqhnbr ='" & TicketNo & "'"
  51.          rsRemote.movefirst
  52.  
  53.  
  54.         with rsLocal
  55.             .addnew
  56.                 !Ticket_No=TicketNo
  57.                 !Date_Entered= Date()
  58.                 !Time_Entered= Time()
  59.                 !Ticket_Date= rsRemote!ioqhdt
  60.                 !Cust_Code= rsRemote!ioqhcustcd
  61.                 !Cust_Name= rsRemote!ioqhcustnm
  62.                 !Cust_Address= rsRemote!
  63.                 !Cust_AddressA= rsRemote!
  64.                 !Cust_Address_2= rsRemote!
  65.                 !Cust_Address_2A= rsRemote!
  66.                 !Cust_City= rsRemote!
  67.                 !Cust_CityA= rsRemote!
  68.                 !Cust_State= rsRemote!
  69.                 !Cust_StateA= rsRemote!
  70.                 !Cust_Zip_Code= rsRemote!
  71.                 !Cust_Zip_CodeA= rsRemote!
  72.                 !Ticket_Type= rsRemote!
  73.                 !Status= "OE"
  74.                 !Sort_Code = "E"
  75.             .update
  76.         End with
  77.  
  78.         set db=nothing
  79.         set conn1=nothing
  80.  
  81.         Me.txtTicketNo = "" 
  82.         Form.Refresh 
  83.         Me.txtTicketNo.SetFocus 
  84.  
  85.         Exit Sub 
  86.  
  87.     Else 
  88.  
  89.         Answer = MsgBox("This ticket is already on today's logsheet!" & vbNewLine & "Would you like to add it anyway?", vbYesNo, "Duplicate Ticket No found") 
  90.  
  91.         If Answer = vbYes Then 
  92.  
  93.             stDocName = "Qry_Get_Ticket" 
  94.             DoCmd.OpenQuery stDocName 
  95.  
  96.             Me.txtTicketNo = "" 
  97.  
  98.             Form.Refresh 
  99.  
  100.             Me.txtTicketNo.SetFocus 
  101.  
  102.             Exit Sub 
  103.  
  104.         Else 
  105.  
  106.             Me.txtTicketNo = "" 
  107.  
  108.             Form.Refresh 
  109.  
  110.             Me.txtTicketNo.SetFocus 
  111.  
  112.             Exit Sub 
  113.  
  114.         End If 
  115.  
  116.     End If 
  117.  
  118.     Exit_txtTicketNo_AfterUpdate: 
  119.  
  120.     RsHdr.Close 
  121.     RsCust.Close 
  122.     RsHdrAddr.Close 
  123.     RsCustAddr.Close 
  124.     RsLookup.Close 
  125.  
  126.     Conn1.Close 
  127.  
  128.     Set RsHdr = Nothing 
  129.     Set RsCust = Nothing 
  130.     Set RsHdrAddr = Nothing 
  131.     Set custhdr = Nothing 
  132.     Set RsLookup = Nothing 
  133.  
  134.     Set Conn1 = Nothing 
  135.  
  136.         Exit Sub 
  137.  
  138.     ERR_txtTicketNo_AfterUpdate: 
  139.         MsgBox Err.Description 
  140.         Resume Exit_txtTicketNo_AfterUpdate 
  141.  
  142. End Sub 
  143.  
  144.  

All I'm doing here is showing you a method. Don't assume this will work as written. The SQL syntax for the informix database type may be different than you have written here so you'll need to check on that. I also didn't complete the field references for the local updates so you'll have to do that yourself.

Again, I'm just giving you one concept on how to do this. There are various other methods as well.
Aug 1 '12 #17

NeoPa
Expert Mod 15k+
P: 31,409
Wesley,

I see you've taken that on board (which is good). Now, though, you're heading into territory that is not the original question. That's not a problem except in as much as it may not be done in this thread (otherwise threads just become messy and impossible to follow sensibly).

At this stage you need to break down your problem into more manageable chunks and ask specific questions for each that you need help with. The accent here is on the specific. We're happy to give help in any area we can, but threads need to be specific to be much use to anyone. The process you have to go through to get to that stage is also very beneficial to you, the requestor, as you have to get much of the logic sorted out in your head before you even ask the question. This will certainly help you with the process, even before anyone jumps in to provide help.

Good luck :-)
Aug 1 '12 #18

Post your reply

Sign in to post your reply or Sign up for a free account.