Is this possible? - update a true/false field using listbox

[OllyJ]

Hi experts, I'm hoping you can help me with this.. I have recently found out how to use the INSERT INTO statement to add multiple records from a listbox to a table, I now need to UPDATE records in another table using a multi-select listbox.

Problem:

I want to present a template of working hours of staff by showing 48 x 30minute intervals, each as a row in a listbox. My idea is that if the person usually works 9am till 5pm, there is a yes / no in the relevant field in the table. I then want the working hours to be modified in the form of a listbox where when loaded it shows the 9-5 rows selected, and it can be modified by selecting more rows / deselecting rows, then saved and the table updated.

...I know how to do it with check / option boxes and i'm sure it can be done with multiselect list boxes but I just don't know how?

Hope you can help, OllyJ

[nico5038]

Guess my sample gives you the idea:
http://www.geocities.com/nico5038/xS...p-Down2000.zip

Just check the use of multiple row selection and the values in the fields at the bottom of the form. (SelStart and SelLength)

Nic;o)

[OllyJ]

Thanks for your response Nico, much appreciated. I have had a look at your sample db...

I understand how you have updated the list boxes in FrmMoveLeftRight (SQL in RowSource) to get the 'true' values in one listbox and the 'false' values in the other. What I need to do is to have all values in one single listbox but on loading the form, the values that are 'true' to be selected and those that are 'false' not selected but still visible.

I hope you can help, many thanks in advance

OllyJ

[nico5038]

The sample database not only shows how to move the records, but more important for you, also how a selection of multiple rows will be recognized.
I would change the sample for your needs by changing the recordsource of the first listbox into selecting all (both true and false) and show the True/False field. The other listbox can be dropped entirely.

Finally you can use the True/False to fill a field that's holding always 48 characters (one for each 30 minutes) with "+" and "-" signs to record the working hours.
Personally I would write two functions:
1) To translate the temporary "hours table" into the 48 charater string
2) To transform the string into 48 records with True and False.

Getting the idea ?

Nic;o)

[OllyJ]

Nic

I am beginning to get the idea however I apologise for my slow take up as I am still very much a novice compared to most and am having to learn as I go.

I'll tell you where i'm at...

I have a table with 2 fields... 'Hours' and 'YesNo'... within 'Hours' there are 48 records (1 for each 30 minutes)... 'YesNo' has a Yes/No data type (there's a surprise!)

Then I have a form bound to that table. In it I have a listbox that contains all of the records for the 'Hours' field. *** I am now struggling with the hard bit - getting the listbox to select those that are true on load of the form and allow editing by selecting / deselecting on listbox and clicking save command button. ***

I am not sure what the two functions that you speak of would do, have I already covered that with how I have set up the table/listbox?

Again, i apologise for my lack of understanding and hope you can help me with this.

OllyJ

[nico5038]

I was under the impression you needed to store the values into one field, thus my proposal for the "code/decode" functions.

I would add a Sequence field to your tblHours so it will hold:
Sequence (Integer 1 till 48)
Hour (Text 0:00 till 23:30)
YesNo (Boolean)

Now you can initialize this table for 9 to 5 using two UPDATE queries like:
UPDATE tblHours Set YesNo = False;
and
UPDATE tblHours Set YesNo = True Where Sequence between 18 and 34;

Finally define a multiselect listbox with the three fields and add a button with the code:

Dim varItm
For Each varItm In Me.lbxTableName.ItemsSelected
   CurrentDb.Execute ("Update tblHours set YesNo = NOT YesNo where Sequence=" & Me.lbxTableName.Column(0, varItm))
Next
Me.lbxTableName.Requery
 

This wil toggle the YesNo field, but you could ofcourse also use two buttons for setting true and false...

Nic;o)

[OllyJ]

I am sorry Nic but I am still struggling to get my head round it. I know I am now into code way above my knowledge level but just have to get this thing done so bear with me please :)....

I understand about adding a sequence column as an 'ID' so to speak.

A couple of questions though

1. Are the queries essential to run the code? Because rather than update to 9-5 I need the user to have complete flexibility by just selecting and deselecting from the multiselect listbox.

2. I tried this code and played with it but kept getting syntax errors....

What does the 'SET YesNo=NOT YesNo' do? I also tried a simple 'SET YesNo = Yes Where...' but couldn't get that to work either.

Private Sub Command40_Click()
 
Dim varItm
 
For Each varItm In Me.List38.ItemsSelected
   CurrentDb.Execute ("UPDATE Table1 SET YesNo = NOT YesNo WHERE Sequence= Me.List38.Column(0, varItm)")
 
Next
Me.List38.Requery
 
End Sub

Also... i've been trying with code for the on current of the form to get those that are 'YES' in the YesNo field to automatically be selected and cannot do that either.

Sorry if you are losing patience :) , i am trying v.hard this end.

OllyJ

[nico5038]

I am sorry Nic but I am still struggling to get my head round it. I know I am now into code way above my knowledge level but just have to get this thing done so bear with me please :)....

I understand about adding a sequence column as an 'ID' so to speak.

A couple of questions though

1. Are the queries essential to run the code? Because rather than update to 9-5 I need the user to have complete flexibility by just selecting and deselecting from the multiselect listbox.

2. I tried this code and played with it but kept getting syntax errors....

What does the 'SET YesNo=NOT YesNo' do? I also tried a simple 'SET YesNo = Yes Where...' but couldn't get that to work either.

Private Sub Command40_Click()
 
Dim varItm
 
For Each varItm In Me.List38.ItemsSelected
   CurrentDb.Execute ("UPDATE Table1 SET YesNo = NOT YesNo WHERE Sequence= Me.List38.Column(0, varItm)")
 
Next
Me.List38.Requery
 
End Sub

Also... i've been trying with code for the on current of the form to get those that are 'YES' in the YesNo field to automatically be selected and cannot do that either.

Sorry if you are losing patience :) , i am trying v.hard this end.

OllyJ

1) No queries aren't necessary, but I would use them to initialize the hours so the user doesn't have to select them for the obvious timespan.
An alternative would be to use only the SET YesNo = False to make sure no old setting remains.
2) 'SET YesNo=NOT YesNo' will switch the value.
When True it becomes False and vise versa.

For testing the needed query use the query text in a new query. (Open a new query, select NO table and press the appearing SQL-button top left)
Just copy/paste:
UPDATE Table1 SET YesNo = NOT YesNo WHERE Sequence = 1;
and press the exclamation button [ ! ] to execute the query.
When something is wrong you'll get a warning.

Keep me posted.

Nic;o)
(PS patience is my middle name :-)

[OllyJ]

Nic

Thanks for your help, I am now able to run this code...

Private Sub Command10_Click()
 
Dim varItm
 
For Each varItm In Me.List6.ItemsSelected
CurrentDb.Execute ("UPDATE [Table1] SET [YesNo] = Yes WHERE [Sequence] =" & Me.List6.Column(2, varItm))
 
Next
Me.List6.Requery
 
End Sub

... I have played around this afternoon for ages trying to find a way to...

Firstly, how to automatically select rows if YesNo is 'true' on loading of the form.

Secondly, how the set the whole YesNo field to 'No' before executing the above code.

If you can help me with these it will be great.

Thanks again for your help so far.

OllyJ

[nico5038]

The problems:
1) Firstly, how to automatically select rows if YesNo is 'true' on loading of the form.
> This is only needed when the "false" rows aren't stored, else all rows an be used. Personally I would probably use a temporary table with the 48 rows and a "decode" function to store the result of the 48 character field into tose rows.
Basically I need to know how your tables have been designed and what way you want to manipulate the data.

2) Secondly, how the set the whole YesNo field to 'No' before executing the above code.
> Initializing the whole temp table is done by:
UPDATE tblHours SET YesNo = False;

Nic;o)

[OllyJ]

All sorted at last... thank you very much for your help Nico.

I found a way around problem1 and problem2 was sorted by using your code... here is the final code i used

Private Sub Command10_Click()
 
CurrentDb.Execute ("UPDATE [Table1] SET [DaysStandard] = NO")
 
Dim varItm
 
For Each varItm In Me.List6.ItemsSelected
CurrentDb.Execute ("UPDATE [Table1] SET [DaysStandard] = Yes WHERE [Sequence] =" & Me.List6.Column(2, varItm))
 
Next
Me.List6.Requery
 
 
End Sub

Many thanks.

OllyJ

[nico5038]

Glad I could help and success with your application !

Nic;o)