combo inside a listbox

[antonyx]

hi.. basic table layout..

http://www.londonheathrowcars.com/db_tables.jpg

have a query that displays jobs with no drivers..

http://www.londonheathrowcars.com/nodriver_qry.jpg

i have seen that combos can be placed in listboxes..

i want the following form that will allow my user to set a driver to each job in the listbox..
so the combo on the right will be present on every row in the listbox.. and if a driver is chosen.. the driverid value is saved..

http://www.londonheathrowcars.com/nodriver_frm.jpg


how can i do this?

 

[boblarson]

A combo in a listbox? That's news to me. Never seen one. Are you sure it wasn't a subform made to look like a listbox?

 

[unclejoe]

Hi antonyx,

First, the combobox is not inside the listbox. It’s on top of the listbox.

Judging from the query, the listbox on the left is using this query.

The listbox on the right using the table called “Driver”.

What you are asking is called a listbox search or listbox to listbox.

The use of combobox is useless is this case. Unless you want to filter the right listbox but it’s unnecessary.

The right listbox, you’ll need to determine the Multi Select property type ‘None’, ‘Simple’ or ‘Extended’. The code created will be base on that.

You can leave the left listbox Row Source empty if you want to.

Normally, you use the OnClick event of the right listbox to filter the left listbox.
Something likes this for a single record filtering.

Me.LeftListBox.RowSource = “SELECT jobdate,jobfrom,jobto,fkdriverid FROM job WHERE fkdriverid = “ & Me.RightListBox.Column(0)

Assuming fkdriverid and driverid datatype is number. Use the qoutes if it’s a text. The Me.RightListBox.Column(0) where 0 is the driverid field.

This code will repopulate the left listbox Row source with the filtered SQL syntax.

hi.. basic table layout..
http://www.londonheathrowcars.com/db_tables.jpg
have a query that displays jobs with no drivers..
http://www.londonheathrowcars.com/nodriver_qry.jpg
i have seen that combos can be placed in listboxes..
i want the following form that will allow my user to set a driver to each job in the listbox..
so the combo on the right will be present on every row in the listbox.. and if a driver is chosen.. the driverid value is saved..
http://www.londonheathrowcars.com/nodriver_frm.jpg
how can i do this?

 

[antonyx]

ok.. yes i have just realised the example i am talking about is not a combo in a listbox.. it looks as if it is.. but really it is loads of combos next to each other..

here is that picture..

http://www.londonheathrowcars.com/dingding.jpg

the form i want is a review and edit form..

the database lists the records that have no drivers and my user allocates a driver to each of these records..

so it would work like this..


http://www.londonheathrowcars.com/dingding2.jpg

this is why i was saying i need a combo on each row of records that have no drivers.. then the user can literally go through the list of records.. and just choose drivers for all of them.. save it.. and hey presto.. all the records with no drivers now have drivers..

does this make more sense?

 

[Bat17]

It does, but I think that you will need to get a bit more complex yet

I would use two listboxes, the first for the jobs with no driver, but there should really be a time slot built in as I assume that your drivers do more than one run a day!.

The second list box with the drivers would need to filter dynamicaly when a job is selected in the first listbox, again the drivers need time slots so that they can handle more than one job per day.
when you filter you can then just select drivers not already allocated to a job at the same time.

you then need a button so that once you have paired a job and driver by selecting one of each in the listboxes you can update the job table and remove them from the choices.

does that make sense?

peter

 

[antonyx]

i think it does make sense.. this form will be a daily form..

it will display all the jobs for a single day.. and my user will allocate drivers to all the jobs on a select day that dont already have a driver registered to them...

let me make the new form.. and show you my interpretation of your idea

 

[antonyx]

ok..here is what the form will look like..

http://www.londonheathrowcars.com/prvw1.jpg

and are you saying that when items are highlighted in each listbox.. and the user presses the button..

like so..

http://www.londonheathrowcars.com/prvw2.jpg

then jimmy will be allocated to the Twickenham 2 Stratford job.. the job listbox will be requeried and the Twickenham 2 Stratford job will be removed from the list..??

if this is the way to do it.. what do i need to do next..

i presume i would need some code on my button.. like the following..

onclick..
For all jobs selected in lstjobs
set lstdriver.value as fkdriverid
lstjobs.Requery

haha. if only it was this simple.. am i on the right track though??

 

[Bat17]

on the right track,but easier to show than tell LOL

This really needs to have a filtering system for the drivers as well though, so that only available drivers are offered.

Peter

 

[antonyx]

thats great..

i think the remove driver may come in handy also.. the problem is when you try to remove 2 drivers in a row.. it doesnt let you.. did you notice that?

 

[Bat17]

nope, I will have a look now though

 

[Bat17]

try changing its code to

Private Sub Command7_Click()
Dim strSql As String
If Nz(Me.[List5], -1) = -1 Then
    MsgBox "Please select driver to delete", vbExclamation
    Exit Sub
End If
strSql = "UPDATE tblJobs SET tblJobs.driverID = 0 WHERE (((tblJobs.JobID)= " & Me.[List5] & "));"
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
Me.List0.Requery
Me.List5.Requery
Me.List5 = -1
End Sub

sloppy code here but it will do to demo

Peter

 

[antonyx]

thanks peter.. if you ever come through heathrow give me a pm and ill arrange a spanking new e-class for ya.

 

[unclejoe]

Hi antonyx,

This is a continuous form with mutli comboboxes and it's on the Detail section.

The three comboboxes on top of the detail section is in the Form Header.

I hope Peter sample can demostrat the workings of a combobox in a continuous form.

If not, look at the Sample at Ken Snell site. Here

It's the sample from Pat HartMan.

ok.. yes i have just realised the example i am talking about is not a combo in a listbox.. it looks as if it is.. but really it is loads of combos next to each other..
here is that picture..
http://www.londonheathrowcars.com/dingding.jpg
does this make more sense?

 

[antonyx]

thank you joe.. that db may prove to be very useful to me in the future..

if i may ask one last favour regarding this form Peter.. (Batman)

basically i want to create a form that deals specifically with one day..

my user will allocate drivers to the pre-bookings at the end of the day.. (which they can do using the form you kindly created for me..)

and they will also have to enter the live jobs taken during the day..

the form I already had.. when it opens a message box appears and asks for the jobdate..

they enter a date.. and this date is saved as the date tag.. and each record they enter in that form automatically is set for that date..

you will see what i mean if you open this db..

anyway.. the last alteration i need is if possible.. when the form loads.. my calendar form loads.. the user chooses a date.. presses ok... and THAT date automatically is the date for the nodriver query listbox and also the date tag..

i think you will see what i mean if you open the db.. this will be the last request on this problem..

thank you for taking the time to listen to my issues.. my company is ever grateful..

 

[antonyx]

let me describe the issue in words so maybe someone can see the solution from the code..

when my form opens.. a message appears and prompts my user to enter a date.. (txtjobdate is the first control in the tab index..)

Private Sub txtjobdate_GotFocus()
Dim dteFormDate As Date
If Me.txtjobdate.Text = "" Then
dteFormDate = CDate(InputBox("Enter the date:", "Date Entry"))
Me.txtjobdate.SetFocus
Me.txtjobdate.Text = dteFormDate
Me.txtjobtime.SetFocus
Else
Me.txtjobtime.SetFocus
End If
End Sub

when more than one record is entered.. i carry the jobdate onto the next record using this on current event..

Private Sub txtjobdate_AfterUpdate()
Me!txtjobdate.Tag = Me!txtjobdate.Value
Me.txtjobtime.SetFocus
End Sub

Private Sub Form_Current()
Me.txtjobdate = Me.txtjobdate.Tag
End Sub

ok.. so basically what i want to do is use the dteFormdate (which is gathered from the initial message box).. and use that date as a criteria for my listbox..

at the moment i have a listbox on the same form that lists jobs with no drivers allocated to them.. driverid=0..

the listbox uses a rowsource of the following..

SELECT tblJob.JobRef, tblJob.JobDate, tblJob.JobTime FROM tblJob WHERE (((tblJob.fkDriverID)=0));

now i tried to do the following..

SELECT tblJob.JobRef, tblJob.JobDate, tblJob.JobTime FROM tblJob WHERE (((tblJob.fkDriverID)=0)) AND ((tblJob.JobDate = dteFormDate));

but the form prompted me for the dteFormDate value immediately..

so how can i alter this scenario to populate the lisbox after the date has been gathered from the message box.??

 

[antonyx]

ok. can anyone see what is wrong with the bold line in this code.. does it make sense?

Private Sub txtjobdate_GotFocus()
Dim dteFormDate As Date
If Me.txtjobdate.Text = "" Then
dteFormDate = CDate(InputBox("Enter the date:", "Date Entry"))
Me.txtjobdate.SetFocus
Me.txtjobdate.Text = dteFormDate
Me.txtjobtime.SetFocus
[b]Me.List0.RowSource = "SELECT tblJob.JobRef, tblJob.JobDate, tblJob.JobTime FROM tblJob WHERE (((tblJob.fkDriverID)=0)) AND ((tblJob.JobDate) = dteFormDate);"[/b]
Else
Me.txtjobtime.SetFocus
End If
End Sub

 

[boblarson]

Me.List0.RowSource = "SELECT tblJob.JobRef, tblJob.JobDate, tblJob.JobTime FROM tblJob WHERE (((tblJob.fkDriverID)=0)) AND ((tblJob.JobDate) = dteFormDate);"

Me.List0.RowSource = "SELECT tblJob.JobRef, tblJob.JobDate, tblJob.JobTime FROM tblJob WHERE (((tblJob.fkDriverID)=0)) AND ((tblJob.JobDate) =[B]#" & dteFormDate & "#[/B]);"

 

[antonyx]

Yes. that has fixed it.. thanks bob.. i knew if i tried to do as much as I could.. it would make this process much easier.. thanks..