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

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

P: 50
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
Dec 20 '07 #1
Share this Question
Share on Google+
11 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Dec 31 '07 #2

P: 50
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
Jan 2 '08 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 2 '08 #4

P: 50
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
Jan 2 '08 #5

nico5038
Expert 2.5K+
P: 3,072
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:

Expand|Select|Wrap|Line Numbers
  1. Dim varItm
  2. For Each varItm In Me.lbxTableName.ItemsSelected
  3.    CurrentDb.Execute ("Update tblHours set YesNo = NOT YesNo where Sequence=" & Me.lbxTableName.Column(0, varItm))
  4. Next
  5. Me.lbxTableName.Requery
  6.  
This wil toggle the YesNo field, but you could ofcourse also use two buttons for setting true and false...

Nic;o)
Jan 2 '08 #6

P: 50
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command40_Click()
  2.  
  3. Dim varItm
  4.  
  5. For Each varItm In Me.List38.ItemsSelected
  6.    CurrentDb.Execute ("UPDATE Table1 SET YesNo = NOT YesNo WHERE Sequence= Me.List38.Column(0, varItm)")
  7.  
  8. Next
  9. Me.List38.Requery
  10.  
  11. 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
Jan 2 '08 #7

nico5038
Expert 2.5K+
P: 3,072
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command40_Click()
  2.  
  3. Dim varItm
  4.  
  5. For Each varItm In Me.List38.ItemsSelected
  6.    CurrentDb.Execute ("UPDATE Table1 SET YesNo = NOT YesNo WHERE Sequence= Me.List38.Column(0, varItm)")
  7.  
  8. Next
  9. Me.List38.Requery
  10.  
  11. 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 :-)
Jan 2 '08 #8

P: 50
Nic

Thanks for your help, I am now able to run this code...
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command10_Click()
  2.  
  3. Dim varItm
  4.  
  5. For Each varItm In Me.List6.ItemsSelected
  6. CurrentDb.Execute ("UPDATE [Table1] SET [YesNo] = Yes WHERE [Sequence] =" & Me.List6.Column(2, varItm))
  7.  
  8. Next
  9. Me.List6.Requery
  10.  
  11. 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
Jan 2 '08 #9

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 2 '08 #10

P: 50
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command10_Click()
  2.  
  3. CurrentDb.Execute ("UPDATE [Table1] SET [DaysStandard] = NO")
  4.  
  5. Dim varItm
  6.  
  7. For Each varItm In Me.List6.ItemsSelected
  8. CurrentDb.Execute ("UPDATE [Table1] SET [DaysStandard] = Yes WHERE [Sequence] =" & Me.List6.Column(2, varItm))
  9.  
  10. Next
  11. Me.List6.Requery
  12.  
  13.  
  14. End Sub
Many thanks.

OllyJ
Jan 4 '08 #11

nico5038
Expert 2.5K+
P: 3,072
Glad I could help and success with your application !

Nic;o)
Jan 4 '08 #12

Post your reply

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