Listbox linked to button on another form (1 Viewer)

[flyers123]

Hi,

I have a form invoice and subform service on main form invoice, what i am trying to do is select the service from another form dropservice that pop's up as listbox. On click of button on subform service next to textbox service description.

Also I would like to be able to select multiple services for one service description textbox and display their total in section price from DropService form to Subform Service field Price.

Could someone help please.

I have attached some pictures and default database to give you some idea more or less what I am trying to do

Thanks in advance

 

[sneuberg]

I don't understand what you are trying to do. In 5.JPG you show the services coma separated in a single field. It would make more sense to me if these were separate records in the Service table. If this were the case you could design it so that multiple services could be picked in the listbox and then code could append records in the Service table but I am just guessing.

Could you explain to us what you are trying to accomplish in ordinary terms? For example I run a barber shop and I need to create an itemized invoice of services like haircut, shave, shoe shine, etc. This type of explanation would help us help you better.

 

[jdraw]

flyers123,

I don't understand your database either. Seems you are missing a customer or supplier, but there is no real context provided.
Can you tell readers in plain English -no jargon - a little about the business and processes involved? What is a dropService?
As sneuberg mentioned --the comma separated list needs attention.

 

[flyers123]

I'm sorry to confuse you

I have a friend works in aged care where the services provided by my friend are i.e. wash, haircut, combup, perm, foils etc.

the services are provided weekly basis and the invoice is charged at the end of each month

I have the following tables company, employees, customers, invoices, service
the last table is called dropservice or selectservice (The job of last table is essentially to allow flexibility to my friend through an form to add an service to existing table without having to go through the coding or altering database in any way through an form from frontend user form)

The database relationship are already linked to the other tables i.e. company, employees, customer and so forth and I have an database working already with similar concept using combobox dropdown though. What I prefers is listbox because using combobox dropdown method the combination of services extend upto 150 and keep on extending slowly. However the listbox method would reduce that to merely 10-13 services and with multiple selection option and total of selected services would work perfectly in this case.

so the situation is my friend provides customer A service in the week1, week2, week3, week4 of a month
if i was to plan the invoice layout as below

Date Servicedescription Price Unit Amount

21/08/2016 Wash $10 1 $10
21/08/2016 Perm $70 1 $70

and so on

the invoice print out would look like 2 pages perhaps with above method in my friend's case

to avoid such instance

What I had advised and my friend agrees to is as below

Date Servicedescription Price Unit Amount

01/08/2016(Week 1) Wash,Comb, Shampoo $50 1 $50
08/08/2016(Week 2) Perm, haircut, wash $110 1 $110
15/08/2016(Week 3) Wash,Comb, Shampoo $50 1 $50
22/08/2016(Week 4) Wash,Comb, Shampoo $50 1 $50
29/08/2016(Week 5) Perm, haircut, wash $110 1 $110

Total $$$$ (Textbox for Total)

I do appreciate for your time and I do apologize for confusing you guys.

I hope this explains the situation bit better and also make more sense

Thank you in advance

 

[jdraw]

Here is a link to a generic data model re: Customers and invoices. Your model may not include all the tables and relationships since this is a generic model of the most common things in Customer and Invoice. You can substitute Service for Product.
Getting your tables and relationships designed to meet your needs; and tested is key to getting a good application.

You could post a jpg of your relationships window for readers to review.

Good luck.

 

[sneuberg]

While a comma separate list of the services might be a good choice for displaying the data you should not store data this way as it would violate normal form. It's good to keep your database in normal form so that it will function properly. Also if you are looking for help on this forum some members won't help you construct a badly designed database albeit they might help you fix the design.

I've attached a simple database that demonstrates how you can keep the data in normal form but display it the way you want. It also has some code that inserts records based on a listbox selection.

Please open the database and open the frmAddAppointment. The CustomerID isn't used in this demonstration so you can ignore that. The listbox on the form has the SelectService table as its Row Source the idea being to display the available services. The listbox is set for simple multiple selection. If you add an appointment date, select some services and click add you will see them in the subform below. This subform isn't connected to the main form and is just there to display the results of the qryService query which is the main thing I want to discuss.

Now please open the Relationships to see how this database is structured. Note that the Service table is a junction table between Appointment and SelectService establishing a many-to-many relationship between them. If you look at the content of the Service table you will see that it's just the primary keys from the related tables. This is all put together in the qryServices query.

Please open the qryService query in design view. Note that this is an aggregate query. It is group by the appointment date and Id and the prices for the services rendered in the appointment are summed. You can use aggregate queries to do this for you if you keep things in normal form. But there's no built-in aggregate that creates a comma separated list so we create one. Note that the Service Types is an expression with the function name GetServiceType. That function is located in the Query Functions module and has the following code:

'This function produces a comma separated list of a query columm
Public Function GetServiceType(AppointmentNo As Long) As Variant

Dim rs As DAO.Recordset
'get the service types for the appointment number
Set rs = CurrentDb.OpenRecordset("SELECT SelectService.ServiceType " & _
                                " FROM SelectService INNER JOIN Service ON SelectService.Service = Service.ServiceNo " & _
                                " WHERE Service.AppointmentNo = " & AppointmentNo)
GetServiceType = Null
'loop through and form comma separated list
Do While Not rs.EOF
    GetServiceType = GetServiceType & rs!ServiceType & ", "
    rs.MoveNext
Loop
'remove the trailing comma
If Len(GetServiceType) > 0 Then
    GetServiceType = Left(GetServiceType, Len(GetServiceType) - 2)
Else
    GetServiceType = Null
End If

This code forms the comma separated list by selecting the Service Types for a given appointment and concatenating them together separated by comas.

Back to the frmAddAppoints form if you look at the on click event code of the Add button you will see that this code segment

'insert records in the service table for each service type selected
For i = 0 To lstServiceType.ListCount - 1
    If lstServiceType.Selected(i) Then
        CurrentDb.Execute "INSERT INTO Service (AppointmentNo, ServiceNo) VALUES( " & Me.AppointmentID & ", " & lstServiceType.Column(0, i) & ")", dbFailOnError
    End If
Next i

shows how you can insert records into the Service table for multiple selections of the listbox.

The code this database is meant just to demonstrate some ideas and is not complete. It would need error checking.

 

[flyers123]

Thank you heaps. I really truly appreciate your generous help. It is absolutely perfect.

However I am having difficulty to implement the code

Please have a look into the picture attached to see what I am trying to do but it's not working

Please help!

Thank you in advance

 

[sneuberg]

I can't see from the images what's wrong. Could you upload the database?

 

[flyers123]

Please find attached the database

Thanks

 

[sneuberg]

You need to finish the basic table structure before you can use the code I posted. For example currently there is no where to store what customer got what service and when. I suggest taking a look at the link Jdraw provided and for each table in that model ask yourself if you don't need that table. You might use different names (appointment vs order) but I think you need most of the tables in that model.

Once you get that done upload the revised database and I'll give you details on how to use the code.

 

[jdraw]

You are missing an InvoiceDetail table that includes the InvoiceNumber, the ServiceID.

Invoice--->InvoiceDetail<---Service

 

[flyers123]

Hi, I'm having terrible issues to have this code implemented still. your code works perfect with in your example but i am not having any luck

what i would like the database to be able to do is -
on the form invoice - user selects the invoice date and the customer from dropdown combobox

on the invoice form there is subform service
- user then clicks on the button select on subform
- that pop's up an another form
- to select the multiple services and add's their total
- once the user click the add button the service information gets added to
the subform service
- that is displayed along with main form invoice.

I hope this makes sense

please help!

 

[sneuberg]

The Invoice form has a subform based on the Service table , a table no longer in the database design if you go by the relationships. I suggest you get the basics of your database working before added the frills. Also when you get to that point it would be a lot easier (and I think user friendly) to have the list box on the Invoice form than in a separate popup.

 

[flyers123]

Hi Steve,

Could you please help

I would like to link invoice number directly to services or invoice number to one appointment and one appointment has services

please help