Reporting column values from ListBox....

[tEkHEd]

HI,

Wondering if someone could give me a helping hand here...

I have a form which holds 2x listboxes. I want to be able to select a value from each column that is selected in the left hand list (lstIColSW) and a value from a specific column name in the the right hand list (lstMLSWList).

lstIColSW is being displayed with the following query:
---------------------------------------------------------

SELECT tblSWInstallBase.Manufacturer, tblSWInstallBase.Product, tblSWInstallBase.Version AS Ver, tblSWInstallBase.Release AS Rls FROM tblSWInstallBase GROUP BY tblSWInstallBase.Manufacturer, tblSWInstallBase.Product, tblSWInstallBase.Version, tblSWInstallBase.Release ORDER BY tblSWInstallBase.Manufacturer, tblSWInstallBase.Product, tblSWInstallBase.Version, tblSWInstallBase.Release;

---------------------E O F-------------------------------

lstMLSWList is being displayed with the following query:
---------------------------------------------------------

SELECT DISTINCT tblMLSoftwareList.Software_ID AS [ML ID], tblMLSoftwareList.Manufacturer, tblMLSoftwareList.Product, tblMLSoftwareList.Version
FROM tblMLSoftwareList
ORDER BY tblMLSoftwareList.Manufacturer, tblMLSoftwareList.Product, tblMLSoftwareList.Version;

---------------------E O F-------------------------------

I want each of the selected values to write data into a new table. I have created a button on the form for this, and have the following VBA code, which has been compiled from a similar question in this forum....

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

Dim dbs As Database
  Dim rst As DAO.Recordset
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("tblSWLink_test") ' - [b]Debugger errors here[/b]
  
 Dim frm As Form, ctl As Control
 Dim varItm As Variant, intI As Integer

  Set frm = Forms!frmSWLink
  Set ctl = frm!lstIColSW
  For Each varItm In ctl.ItemsSelected
      For intI = 0 To ctl.ColumnCount - 1
          MsgBox ctl.Column(intI, varItm)
          rst.AddNew
          rst!Manufacturer = Me.lstIColSW.Value    '- [b]The next value needs to go to a [u]different field[/u] in the destination table[/b]
      Next intI
  Next varItm
 rst!AppID = Me.lstMLSWList.Value
 rst.Update
 rst.Close
End Sub

---------------------E O F-------------------------------

There are currently 2 issues with this code...

1: I want each of the seperate reported values, to be entered into unique fields in a new table, which will be used as a reference for SWInstallations. At the moment I cannot figure out how to put Value 2 into a seperate field (It currently goes into "Manufacturer" as I have coded)

2: I also get the following error:

"error 3393

Cannot perform join, group, sort, or indexed restriction. A value being searched or sorted on is too long."

Again can anyone explain this?? I have used a similar code (without differing values being written into a table, i.e. the same value being entered into each field works) and do not get any errors at all... / Searchin the web doesn't really help out either...

Thanks in Adv for any help/advise/resources that you can give...

 

[SBendBuckeye]

Have you considered doing this with SQL? Check out the Insert Into statement. The basic syntax for inserting one row at a time is as follows:

Dim strSQL As String
'Define as much as we can before hand
strSQL = "Insert Into TblName (Fld1, Fld2, Fld3) " _
& "Values ("
For Each varItm In ctl.ItemsSelected
DoCmd.SetWarnings False 'Stop the About to Append msg
'Generate the rest of the SQL on the fly
DoCmd.RunSQL strSQL & ctl.Column(0, varItm) & ", " _
& ctl.Column(1, varItm) & ", " _
& ctl.Column(2, varItm) & ""
DoCmd.SetWarnings True
Next varItm

NOTE: When building SQL on the fly like this, you need to observe the same restrictions for criteria that exist for DCount, DLookup, etc. Strings are surrounded by single quotes ', dates by pound signs # and numerics by nothing.

In the example above, if Fld1 is text, Fld2 is numeric and Fld3 is a date it would look like this after collapsing the whole ctl.Column etc into ctlCol for illustration purposes:

'strSQL ends with Value (
DoCmd.RunSQL strSQL _
& "'" & ctlCol1 & "', " & ctlCol2 & ", #" & ctlCol3 & "#)"

To me, SQL is a lot simpler to follow, plus if you dump your code into the query SQL section you can test it in non-update mode until you get it working the way you want it to.

Actually, you might be able to create an Append query and let the builder create all or much of the ugly stuff above for you then it becomes DoCmd.RunSQL qryForm1UpdateSQL.

I haven't ever actually done the last step so I don't know if it will work or not.

Hope this helps and Good Luck!

 

[tEkHEd]

Hey SBendBuckeye!

Thanks for that code, I think that this is the way to go...

I still get an error though at the mo...

Error 3075

Syntax Error (Missing Operator) in query expression '(Value1, Value2, Value3, Value4)'

In my case the Values are:

1 = Adaptec
2 = Easy CD Creator
3 = 3
4 = 0

(Adaptec, Easy CD Creator, 3, 0)

The code I have is:

Dim strSQLWTbl As String

strSQLWTbl = "Insert Into tblSWLink_test (Manufacturer, Product, Version, Release) " _
& "Values ("
For Each varItm In ctl.ItemsSelected
DoCmd.SetWarnings False

DoCmd.RunSQL strSQLWTbl & "(" & ctl.Column(0, varItm) & ", " _
& ctl.Column(1, varItm) & ", " _
& ctl.Column(2, varItm) & ", " _
& ctl.Column(3, varItm) & &quot);"
DoCmd.SetWarnings True
Next varItm

Is this error because I have spaces in the 2nd VarItm?? would I need to put each of these in another set of quotes?

Tks in adv...

 

[tEkHEd]

Amendment...

I I run the Query in SQL Query Analyser as:

Insert Into tblSWLink_test (Manufacturer, Product, Version, Release) Value (1, 2, 3, 4)

I can run the query...

If I run it as:

Insert Into tblSWLink_test (Manufacturer, Product, Version, Release) Value (adaptec, easy cd creator, 3, 0)

I get the following error:

Server: Msg 128, Level 15, State 1, Line 1
The name 'adaptec' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

so does this meant that the pass through query in my VBA statement would not be working because of the same error?? How can I tell SQL that the values are field entries rather than column names?

 

[tEkHEd]

Or can I refer to each column in SQL as its' own control (like [Forms]![frmMyForm]![conMyControl])

If I can refer to each column, then I can base a query on the selected item and get SQL to write the output of the query into each of the req fields..

 

[tEkHEd]

another move in the right direction.. I now have managed to get the output in the error box (3075) matching what I have entered in the Query analyser..

('Adaptec', 'Easy CD Creator', '3', '0')

by using the following series of ' " ' " ' " 's..

After a few mins of moving around the inverts I end up with this:

Dim strSQLWTbl As String

strSQLWTbl = "Insert Into tblSWLink_test (Manufacturer, Product, Version, Release) " _
& "Values ("
For Each varItm In ctl.ItemsSelected
DoCmd.SetWarnings False

DoCmd.RunSQL strSQLWTbl & "'" & ctl.Column(0, varItm) & "'" & ", " _
& "'" & ctl.Column(1, varItm) & "'" & ", " _
& "'" & ctl.Column(2, varItm) & "'" & ", " _
& "'" & ctl.Column(3, varItm) & "'" & &quot"
DoCmd.SetWarnings True
Next varItm

This works

 

[tEkHEd]

another move in the right direction.. I now have managed to get the output in the error box (3075) matching what I have entered in the Query analyser..

('Adaptec', 'Easy CD Creator', '3', '0')

by using the following series of ' " ' " ' " 's..

After a few mins of moving around the inverts I end up with this:

Dim strSQLWTbl As String
   
   strSQLWTbl = "Insert Into tblSWLink_test (Manufacturer, Product, Version, Release) " _
          & "Values ("
          For Each varItm In ctl.ItemsSelected
      DoCmd.SetWarnings False
   
      DoCmd.RunSQL strSQLWTbl & "'" & ctl.Column(0, varItm) & "'" & ", " _
                              & "'" & ctl.Column(1, varItm) & "'" & ", " _
                              & "'" & ctl.Column(2, varItm) & "'" & ", " _
                              & "'" & ctl.Column(3, varItm) & "'" & ")"
      DoCmd.SetWarnings True
   Next varItm

This works

 

[SBendBuckeye]

It looks like you figured it out on your own, but buried in my example at the top is a note that string items in the value list must be surrounded by single quotes ' like so:

Insert Into tblName (fld1, fld2, fld3, fld4) _
Values ('fld1', fld2, 'fld3', #fld4#)

if fld1 and fld3 are string and fld4 is a date. Nice job of figuring things out on your own. Much easier than spinning a recordset, don't you think?

Good Luck!

 

[tEkHEd]

yeah I should learn to read properly if I am gonna ask for help

I now just have to figure out how to get a column in the 2nd list box written also without it going thru the FOR ECH VAR loop....

 

[SBendBuckeye]

I'm not sure what you mean by your question. Can you explain a little more fully what you are trying to do?

 

[tEkHEd]

Ok,

My forma has 2 x list boxes.. The one on the left has 4 columns which need their data inputting into a new table, the code for which has been discussed above.

The list on the right needs to have the first column's data written into the same table to allow linking of 2 tables which do not have a 1 to 1 relationship.. The relation ship will be made in the new table, by linking ID01 (left hand column) to ID02 (right hand column) to a new table providing and ID03.

I want to know where abouts in the code example above, I can put in another clause that will read data from Column (0) in the right hand table and output that value into the new linking table...

Hope that this explains more clearly what I am trying to achieve...

 

[tEkHEd]

I have sorted this out now as well...

With the following code I am able to report the selected values in the lefthand list, and a dedicated value from the right hand list, entering these values into a new table so that I can provide a 1 to 1 relationship


SBendBuckeye Thanks very much for all of your help with this, and I will prob reply to this thread if I have any further problems with this Software Licensing Applet that I am building..

Thanks.

 

[tEkHEd]

BTW here is the final code... all that I need to do know is tweak it so that once the match has been made between the 2 lists, that each item in each of the lists is removed so that there is no duplication..

   Dim frm As Form
   Dim ctl As Control
   Dim ctl2 As Control
   Dim varItm2 As Variant
   Dim varItm As Variant
   Dim intI As Integer

   Set frm = Forms!frmSWLink
   Set ctl = frm!lstIColSW
   Set ctl2 = frm!lstMLSWList
   
    
   Dim strSQLWTbl As String
   Dim strSQLWTbl2 As String
   
   strSQLWTbl = "Insert Into tblSWLink (Manufacturer, Product, Version, Release, MerrillAppID) " _
          & "Values ("
          
          For Each varItm In ctl.ItemsSelected
          For Each varItm2 In ctl2.ItemsSelected
          
      DoCmd.SetWarnings False
  
      DoCmd.RunSQL strSQLWTbl & "'" & ctl.Column(0, varItm) & "'" & ", " _
                              & "'" & ctl.Column(1, varItm) & "'" & ", " _
                              & "'" & ctl.Column(2, varItm) & "'" & ", " _
                              & "'" & ctl.Column(3, varItm) & "'" & ", " _
                              & "'" & ctl2.Column(0, varItm2) & "'" & ")"

          
          DoCmd.SetWarnings True
     Next varItm2
     Next varItm
   
 End Sub

The new table that I have created has an AutoGen field, which could be used as the identifier for the software (I am having to marry up purchased license information with actual installations on machines, the format of these 2 tables is not identical, and therefore we need to create the new ID to be able to link the 2 tables together..)

I table that holds the installs info (the left hand column..) gets overwritten every 2 weeks as part of an automated audit... The new table should start to contain the information in the "installs" table, along with an internal ID number so that when the table gets re-created and imported into the form, it should check to see if it has already been identified, by checking the new table for Man, Prod, Vers, Rls and the ID number. If it does find this info, the form doesn't show that soft in the list.. eventually we should have an identifier for each piece of SW in use...

Make sense???