Need help with company database and form setup (1 Viewer)

flyers123

Registered User.
Local time
Tomorrow, 06:04
Joined
Aug 20, 2016
Messages
21
Hi
The example attached is just a suggestion and there may be areas that need further work.

The Combobox for services only shows those services which are currently Active.
Hi Mike, Thank you for sharing the example

I greatly appreciate it

However, whilst I was testing it out I found that

If I select the acupuncture service and create a new invoice, then make the acupuncture service inactive, it disappears from the the previous invoice.

What I was hoping to achieve was even if I select inactive for any of the services after I have issued the invoice, it should still appear on the previous invoice.

The date of service as it is in your example can only be selected once, however I was hoping to have the option to select date each line item

The reason being if I provide acupuncture service to Bill on 20/01/2021 and bill does not want to have Yoga on the same day, i will need to go another day to provide Yoga service, but it will be invoiced on the same invoice as different service date.

Lastly when create new record and select company from combo box right at the top, it does not let me create new record and keep showing the old data as soon as i select company 1, super confused with this one.

How can I display the total of line item?

Thank you in advance
 

Attachments

  • Screenshot 2021-02-21 083023.jpg
    Screenshot 2021-02-21 083023.jpg
    83.5 KB · Views: 107
  • Untitled.png
    Untitled.png
    55.4 KB · Views: 112
  • Screenshot 2021-02-21 082611.jpg
    Screenshot 2021-02-21 082611.jpg
    65.3 KB · Views: 107
  • Screenshot 2021-02-21 082439.jpg
    Screenshot 2021-02-21 082439.jpg
    94.3 KB · Views: 109

jdraw

Super Moderator
Staff member
Local time
Today, 15:34
Joined
Jan 23, 2006
Messages
15,364
Consider, in a situation where Items are ordered by a customer and you are getting the Price/Cost of that Item from the Item table, then old invoices will be "meaningless/inaccurate" whenever you change the Item price in the Item table. Seems similar to your inactive service, if I understand correctly. The Item(and service) tables deal with today/at this moment/currently. But the invoice or Item purchase was "in the past", so when you want to use the "current value" when looking/reporting and old invoice/service you have a logical inconsistency.

The selling price at the time of sale of the Item should be recorded in the OrderDetail/LineItem record.
Similarly you need to record sufficient info with the invoice to prevent/avoid your current situation.
Good luck.
 

flyers123

Registered User.
Local time
Tomorrow, 06:04
Joined
Aug 20, 2016
Messages
21
maybe you can use this remark in a SQL query as Rowsource for the combo? Active = 1, notActive = 0
Code:
SELECT Services.Service, Services.Price, Services.ServiceID, Services.Active
FROM Services ORDER BY Services.Active DESC, Services.ServiceID
Hi Gemma,

Thank you for the suggestion.

It seems valid point, however I was having hard time implementing the code behind the logic.

Can you please me help me understand how could I implement this code into Mike's example database above.

Thank you for your help in advance.
 

mike60smart

Registered User.
Local time
Today, 19:34
Joined
Aug 6, 2017
Messages
1,899
Hi Gemma,

Thank you for the suggestion.

It seems valid point, however I was having hard time implementing the code behind the logic.

Can you please me help me understand how could I implement this code into Mike's example database above.

Thank you for your help in advance.
Hi
My original database modified so that you can now add a Service Date for each Service.

When you select a Customer it displays the customer selected
It also displays the Previous Service's for the Customer Selected
To add a new service click the Add New Service Command Button.

Once you have added all details required you can then click the Preview Invoice Command Button

Again this is just one way to do this.
 

Attachments

  • Contractor2.zip
    124.3 KB · Views: 114

flyers123

Registered User.
Local time
Tomorrow, 06:04
Joined
Aug 20, 2016
Messages
21
Hi
My original database modified so that you can now add a Service Date for each Service.

When you select a Customer it displays the customer selected
It also displays the Previous Service's for the Customer Selected
To add a new service click the Add New Service Command Button.

Once you have added all details required you can then click the Preview Invoice Command Button

Again this is just one way to do this.
Hi Mike,

Appreciate your update with this database.

However, after adding the service massage in 3 invoices, when i made massage service inactive in the table "tbluservices" it disappeared from the invoices and shows blank fields.

I have attached the database for you. That is the problem i was having to understand how to display the service that has been made inactive the previous invoices.

Thank you for your time and efforts
 

Attachments

  • Contractor2.accdb
    996 KB · Views: 121

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:34
Joined
May 7, 2009
Messages
19,169
so don't flag any as Inactive. only you knows who is Active or Inactive.
your customer don't know about it.
 

mike60smart

Registered User.
Local time
Today, 19:34
Joined
Aug 6, 2017
Messages
1,899
Hi Mike,

Appreciate your update with this database.

However, after adding the service massage in 3 invoices, when i made massage service inactive in the table "tbluservices" it disappeared from the invoices and shows blank fields.

I have attached the database for you. That is the problem i was having to understand how to display the service that has been made inactive the previous invoices.

Thank you for your time and efforts
Hi

OK Check this version
 

Attachments

  • Contractor2.zip
    118.2 KB · Views: 130

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:34
Joined
May 7, 2009
Messages
19,169
i have not used any of your table.
this is but a demo.
what i mean is pay attention to your Forms and Reports.
must be user-friendly.
 

Attachments

  • specialservice.accdb
    1.6 MB · Views: 121

flyers123

Registered User.
Local time
Tomorrow, 06:04
Joined
Aug 20, 2016
Messages
21
Hi

OK Check this version
Hi Mike,

That's perfect and works like a charm!

I'm extremely grateful to you for your time.

I have 2 more questions, hope you don't mind.

Question 1) Can the same thing be done for employee and contractor dropdown box as well as it does for services. meaning after the contractor or employee has been made inactive it still shows the historical information for past invoices.

Question 2) When a service is selected from dropdown, it has price set already and auto populates in the price field.

Thank you in advance.
 

flyers123

Registered User.
Local time
Tomorrow, 06:04
Joined
Aug 20, 2016
Messages
21
Hi

OK Check this version
Hi Mike,

I haven't had any update since last 2 questions.

I guess you might have been busy.

Can you please assist with

Question 1) Can the same thing be done for employee and contractor dropdown box as well as it does for services. meaning after the contractor or employee has been made inactive it still shows the historical information for past invoices.

Question 2) When a service is selected from dropdown, it has price set already and auto populates in the price field.

Thank you in advance
 

mike60smart

Registered User.
Local time
Today, 19:34
Joined
Aug 6, 2017
Messages
1,899
Hi

Check this version
 

Attachments

  • Contractor3.zip
    70.6 KB · Views: 111

Users who are viewing this thread

Top Bottom