Another 2003 List box question

[perldiver]

A little background info,
I am creating a Incident Log for an organization that deals with troubled child.
This log is a continuation of work already in place that tracks numerous aspects of the childs treatment. All database tables are stored on Server2003, MySQL then linked to the Access front end via MySQL ODBC 3.51.

Problem: Their is a need during data entry, to use a list box that will multi select staff members involved in the specific incident with a child on that given day. The form data is shared between these tables right now......

tbincident_log
incidentlog_id (index & PK)
child_id (FK one to one relationship with numerous other tables)
Stamp (datetime)
Staff (var shouldn't be here but is... )
Starttime (time)
StopTime (time)
Reasons ( memo)
restraint_codes (var) comb box on form populated via query no problems


tbStaff (used to populate list box via query with no problems)
staff_id (index & PK)
staff_name (var)

tbincident
incident_id (index & PK)
staff_id (int & FK)

so that's about it. I know, I need a one to many relationship and some vb on a command button to insert selections and to return results during review of the files probably back to the multi select box as a color red . Also that this selection data needs to be in tables for normalization. My last brain cell crashed last night and with reading all day today it's not coming to me were to go next. Any advice really appreciated.

Thanks
Tom

 

[unclejoe]

Hi Tom,

Not a good table design, as I don’t know how the workflow and the form design works, I can’t say much.

In the table “tbincident_log” the column/field “Staff” data type is “Text” or some alpha charactors?

For a start, lets try… in your command button… (warning air code here)
(List0 is the listbox name)

Dim strConcate as String
Dim i as Integer

For i = 0 To List0.ListIndex
If List0.Selected(i) then
strConcate = strConcate & List0.Column(1) & “,”  ‘ staff name
End If
Next i

‘fill the staff names into textbox, assume you have a textbox named “Staff”
Me.Staff = Left(strConcate, Len(strConcate) –1)   ‘remove last comma

HTH

 

[perldiver]

HTH,

Thanks for the rapid response. I have a couple of questions. The reference to the staff field was a mistake, in that it is text vise var. Your comment about "not a good table design", what's so obviously wrong to warrant the comment ? As for the VB, why are we filling in a textbox with the listbox selections that appears to be comma delimited? The approach is somewhat unclear to me at the moment, sorry. I believe, I may have misled you with the comment about reviewing the records. Once the information is inserted into the database by clinical help, it will ultimately be reviewed or printed out by Therapists at weeks end or some later date.

A little more info about the configuration of things. Hope this helps

The Form incident_log is displayed in form view. The control source is the tbincident_log. Stamp is 'timestamp' default CURRENT_TIME and not displayed on the form. Start / Stop Time is approx. times of the day for that given incident. Oh Yes, I do have a problem with the time and using MySQL. I hopefully can work that out later. The child_id is used to track which child the incident is related to. Hence the FK in the incident_log.

LMK- if I can clarify anything for you to make sense of this.

Tom

 

[perldiver]

HTH,

In looking over this it dawned on me that I mentioned the Staff field in the incident_log shouldn't be there. I don't want to give you the impression that I wanted to insert the selections from the listbox into the staff field of the incident_log as a comma delimited string. I'd rather store them individually with a table. Say Tbincident

Just a thought
Tom

 

[unclejoe]

Hi Tom,

Seems much clearer now. Should have asked you about “tbincident” table usage and relationship.

By now you should be able to get the idea on how to insert records by listbox.
Here’s a sample for you to start with.

‘code in your command button
Dim strSQL as String
Dim strIN as String
Dim i as Integer

‘create a SQL syntax, watch for word wrap
‘word of caution, the SQL syntax might be different for MySQL
strSQL = “INSERT INTO tbincident SELECT tbincident_log.incidentlog_id, tbStaff.staff_id FROM tbstaff, tbincident_log WHERE tbStaff.staff_id IN (" & strIN &")”

‘concatenate the selected staff_id with the SQL (strIN)
For i = 0 To List0.ListIndex
If List0.Selected(i) Then
strIN = strIN & List0.Column(0) & “,” ‘ staff ID
End If
Next i
strIN = Left(strIN, Len(strIN) –1) ‘remove the last comma

‘disable warning messages
Docmd.SetWarning False
Docmd.RunSQL strSQL ‘run sql to insert record into tbincident
Docmd.SetWarning True

As for the date/time issues, please search the forums here regarding MySQL with MSAccess. There was more than one posting on this.

HTH,
In looking over this it dawned on me that I mentioned the Staff field in the incident_log shouldn't be there. I don't want to give you the impression that I wanted to insert the selections from the listbox into the staff field of the incident_log as a comma delimited string. I'd rather store them individually with a table. Say Tbincident
Just a thought
Tom

 

[perldiver]

HTH,

Tried the code on the command button .... made a few name changes to match tables renames so here's how it reads......

Private Sub Command2_Click()
'code in your command button
Dim strSQL As String
Dim strIN As String
Dim i As Integer

'create a SQL syntax, watch for word wrap
'word of caution, the SQL syntax might be different for MySQL
strSQL = "INSERT INTO incident SELECT incident_log.incidentlog_id, Staff.staff_id FROM staff, incident_log WHERE Staff.staff_id IN (" & strIN & ")"

'concatenate the selected staff_id with the SQL (strIN)
For i = 0 To List0.ListIndex
If List0.Selected(i) Then
strIN = strIN & List0.Column(0) & "," 'staff ID
End If
Next i
strIN = Left(strIN, Len(strIN) - 1)

'disable warning messages
'DoCmd.SetWarning False
DoCmd.RunSQL strSQL 'run sql to insert record into incident
'DoCmd.SetWarning True
End Sub

Failure comes as Syntax error (missing operator) in query expression 'Staff.staff_id IN ()'.

It seems that the issue is (" & strIN & ")". From what I've read about the IN Function in MySQL the typical format is IN ('9', '10', '11'); Tried that and got a data type mismatch in expression. Any ideas ?

Tom

 

[unclejoe]

Failure comes as Syntax error (missing operator) in query expression 'Staff.staff_id IN ()'.

It appears that there was no selection of staff_id. You need to make sure that the “for next loop” doe not exit else it will not work. Use the VBA editor, insert breakpoints to trace the errors. And make sure the listbox name is called “List0” or rename “List0” according to what you have named it on the listbox property.

Is the “ID” a text or number? If text, use or include the single quotes. The data of each “ID” must match both column/fields of the tables.

It seems that the issue is (" & strIN & ")". From what I've read about the IN Function in MySQL the typical format is IN ('9', '10', '11'); Tried that and got a data type mismatch in expression. Any ideas ?

It seems that Access and MySQL have different syntax.(if you did a search).
Let’s try a different approach with MySQL syntax. (If the tables are Link by ODBC, I think there shouldn’t be any problems with Access SQL syntax.). I think I have omited the fields for the insert statement.

‘use the debug.print to output the string for syntax error correction
strSQL = "INSERT INTO incident (incident_id, staff_id) ” ‘note of space at last charactor
Debug.Print strSQL
strSQL = strSQL & “SELECT incident_log.incidentlog_id, Staff.staff_id FROM staff, incident_log WHERE Staff.staff_id IN ("
Debug.Print strSQL
strSQL = strSQL & strIN &”)”
Debug.Print strSQL
‘look at the immediate window, the end of the string is some like “ …..IN ( )
‘that’s because the code have not process the “for next loop”

‘Insert a debug line just after this

strIN = Left(strIN, Len(strIN) - 1)
Debug.Print strIN    ‘the output of the IN string
Debug.Print strSQL    ‘the output of the SQL syntax with the IN string
‘after the for next loop is completed, you should see something like “IN ‘(1,2,3…)” in the last debug.print.

If the “ID” are text, include the single qoutes like….

strIN = strIN & "'" & List0.Column(0) &”’” & “,” ‘ staff ID
….
strIN = Left(strIN, Len(strIN) - 1) ‘remove last comma

PS. “HTH” is not my name, it’s short for “Hope This Helps”.

 

[perldiver]

Thanks for the reply Joe?
staff_id as it relates to tbincident is an (int).

What is populating the list box is a simple query "select staff.* from staff;" the output in the unbound list box should be , Two columns staff_id and staff_name. Staff_id in this cause is an integer, and staff_name is text. column count is 2, column width is 0";1" , bound column is 1. The list box is set to simple and the list box name is List0. I hope this is all correctly set, if not LMK. Unfortunately I'm not at work to access the database to try these changes tonight. I have a test_database setup on our server that I can trash without fear for affecting anything.
I really appreciate the help.....
Tom

 

[unclejoe]

Hi tom,

As long as the listbox name and rowsource is correct as in your case, your first column is “staff_id” there is no problem. As for the lisbox “Multi Select” property, why “Simple”? In your original post, you said about selecting multiple staff id’s records into tbincident with the listbox.

Problem: Their is a need during data entry, to use a list box that will multi select staff members involved in the specific incident with a child on that given day. The form data is shared between these tables right now......

The sample code I gave will still work for listbox with “Multi Select” property set to “Simple”. However, if you set it as “None”, you might as well use Combo Box to select a single staff_id. Would "Extended" be a better choice? Just checking.

Thanks for the reply Joe?
staff_id as it relates to tbincident is an (int).
What is populating the list box is a simple query "select staff.* from staff;" the output in the unbound list box should be , Two columns staff_id and staff_name. Staff_id in this cause is an integer, and staff_name is text. column count is 2, column width is 0";1" , bound column is 1. The list box is set to simple and the list box name is List0. I hope this is all correctly set, if not LMK. Unfortunately I'm not at work to access the database to try these changes tonight. I have a test_database setup on our server that I can trash without fear for affecting anything.
I really appreciate the help.....
Tom

 

[perldiver]

Joe,

I'm headed to the office now to try the code changes..... yes "extended" would be a better choice. Oversight on my part.....

Thanks again
Tom

 

[perldiver]

Joe,

I'm getting the feeling this can't be done and that I'm wasting your time like my own trying.
Here is the new problem with this list box approach. When the data entry person selects the record for the child requiring some updates, they are presented with a central form that contains five tabs. As they select each tab related information is present provided by the linked tables. The data entry personal would be able to update existing data records or create new as required. The incident log would be no different. Upon selecting the Incident Log tab for that child it would open the form in formview at record 1 of 1 if no previous entries existed. At this point, the child_id is pre-filled for them for continuity. The remaining fields including our list box would need to be filled in accordingly. When the Data enter person advances the record or exits the form the data is inserted into the database.

So the problem with using the command button is that there are no values for the select function of strsql until after the other form data is inserted...

"INSERT INTO incident SELECT incident_log.incidentlog_id, staff.staff_id FROM incident_log, staff WHERE staff.staff_id IN ('strIN')

.... would result with a incidentlog_id (null), staff_id as a string from the list box trying to be inserted into tbincident, thus here come the errors.

Finally after using the VB editor, I could see a couple of things that I'm not sure matter all things considered ..... Example... selecting the 1st and 3rd person on the list resulted in the strIN = (3,3) vise (1,3) the syntax error is still there but .... let's say I select 6 people 1-6 on the list. The strIN displays (6,6,6,6,6,6) very strange.......

Well, Joe I think I need to come up with another way of doing this all together. Sorry for wasting your time, but you've been very helpful in my understanding on how this list box works.

Tom

 

[unclejoe]

Hi Tom,

I hope you can see the mistake below, let's try one more time

For i = 0 To List0.ListIndex
If List0.Selected(i) Then
'mistake here
'strIN = strIN & List0.Column(0) & "," 'staff ID
'correction here
strIN = strIN & List0.Column(0,i) & "," 'staff ID
End If
Next i
strIN = Left(strIN, Len(strIN) - 1)

if it doe not work try... "strIN = strIN & List0.ItemData(i) & "," 'staff ID"
Both of these should work.

snip

Joe,
Finally after using the VB editor, I could see a couple of things that I'm not sure matter all things considered ..... Example... selecting the 1st and 3rd person on the list resulted in the strIN = (3,3) vise (1,3) the syntax error is still there but .... let's say I select 6 people 1-6 on the list. The strIN displays (6,6,6,6,6,6) very strange.......

Well, Joe I think I need to come up with another way of doing this all together. Sorry for wasting your time, but you've been very helpful in my understanding on how this list box works.
Tom