WorkOrder Seminar and More - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

WorkOrder Seminar and More

Julian Okolonji

5 years ago

Hi All

First, I'll like to say a big thankyou to Richard for pointing me to this forum and also for the wonderful work he's been doing with the Tech Help Videos and courses.

I have combined the ABCD, Work Order Seminar, Time and billing, Payables and Email seminar to create a wonderful database for my small Temp staffing Agency and it works really well, but there are some features I'll like to add and was wondering if I could get help here.

1. I added a complaints table & form so that I can keep records of clients complaints of our workers. I'll be very grateful if someone can show me how to mark a worker banned but only banned from the particular client who has complained and will no longer want the worker again. I have a check box to indicate that the worker is banned on the complaints form and when ticked as banned he/she should not appear in the WorkerCombo when trying to place workers to that client or if they appears he/she cannot be selected/ a warning message saying this worker cannot be placed for this assignment.

2. When workers are booked for an assignment I'll like to be able to send assignment details to them via text or WhatsApp messages directly from my database.

AND ...

3 How to setup a parameter query in VBA with 3 criteria's in one inputbox.eg "ClientName", "StartDate" and "EndDate" instead of 3 input boxes popping up one after the Other.

I was hoping that Richard must have covered these in one or more of courses so I'll be grateful if you can point me to the right course or Courses. Or maybe someone can help here on the forum. I'm also ready to pay for any of the above that is complicated to setup or will take time to accomplish.

Please help

Many thanks in advance

Julian Okolonji
United Kingdom

	Julian Okolonji OP @Reply 5 years ago

Adam Schwanz @Reply

5 years ago

#1 Use a many to many relationship, so you can assign which workers are banned to which customers. Then filter those out of your list. Many-to-Many

#2 you can e-mail to text with most cell phone providers, I would go with this option

#3 This would probably be more difficult than it's worth. You would need to find a way to separate which parts of the text represent each part, and with differing name sizes, people with two last names or names that would prevent you from saying find the first space character and then cut off that section, this sounds like a pain. Are any of these fields available on the form already so you could just call the value and not have to re-enter it?

Scott Axton @Reply

5 years ago

Next course:
Search Seminar

This one is a little older but it is definitely stood the test of time.  In my opinion worth it's weight in gold.

# 3 kind of goes with the search suggestion.  You might consider a little pop up form where you could enter all three criteria then use those values in your query using the Forms!FormName!FieldName notation.

See Value From a Form if you need a refresher.

	Julian Okolonji OP @Reply 5 years ago

	Julian Okolonji OP @Reply 5 years ago
Adam, #1 I have learnt the many to many relationship with a junction table but not sure how to implement this in my situation to solve my problem. #2 I have email already built into my database but dont know how to set it up for sending text messages. Scott thanks for the reply for #3. I have tried that metho, it works well when a name is searched but my dates return dates outside my criteria

Adam Schwanz @Reply

5 years ago

#1. Just make a form to assign customer and worker who are not allowed togethor. It can be a seperate form or a subform on that form. Then set the criteria on the combobox to only show workers who are not blocked from that customer. Or are you looking for help with the rowsource?

#2 you just use an e-mail. The emails to send texts are like for US Cellular, [email protected] assuming since they're your workers you'd be able to get their phone provider pretty easily.

Richard Rost @Reply

5 years ago

First, thank you for the compliments.

1. Definitely learn many-to-many relationships. The Relationship Seminar covers them in detail, also the TechHelp that Adam referred you to.

2. You can probably figure out the email-to-text gateways for your employees like Adam recommended. Sending emails to text that way is real easy. I'm going to be doing a TechHelp in the future how to use an API gateway to send texts directly without knowing this information, but that's still a little ways off.

3. Make a custom form for it and then have it return the values where they need to go.

Julian Okolonji OP @Reply

5 years ago

Thank you all for coming to my rescue, I have figured out how to deal with the pop up form with three criterias from the search seminar as Scott suggested :) , I will also be sending text using email-to-text which is very helpful at the moment but cant wait for the TechHelp as Richard said he'll be doing that in the future.

Now my last problem is the many to many relationship and I hope I don't appear very dumb. I watched the TechHelp video and the extended cut again but still can't figure out how to set this up in my situation. I know how to use many to many when it comes to things like Customer to mailing list, Students to courses and so on but can't figure out how to use it for my situation.

I have a ClientT, BannedT & WorkerT. can you please suggest how I can set this up, which is going to be the junction table. I have tried setting up a WorkerClient many to many relationship but were does the bannedT come in or is the BannedT meant to be the junction table?

	Adam Schwanz @Reply 5 years ago
BannedT should be the junction table. All you should need is your customerid from customert and your workerid from workert. Put them togethor in the junction table and thats all you need. You could also add like a notes field or a banned reason or anything else to that junction table that you want to track about the specific problem.

	Adam Schwanz @Reply 5 years ago
Client not customer in your case

	Richard Rost @Reply 5 years ago
Adam is 100% correct. Watch Access Expert 7 again.

	Scott Axton @Reply 5 years ago
Julian - If your form is not returning the desired results (dates outside of the criteria) you might not be asking the right question so to speak. If you can describe what you want to do and show us the code behind the search form when can help trouble shoot better than "it doesn't work"

	Julian Okolonji OP @Reply 5 years ago

Julian Okolonji OP @Reply

5 years ago

Wow you guys are amazing, very willing to help.

I have watched Expert 7 and feel that's exactly what I have done as shown in the picture above (ClientF). The problem is I have another form called ShiftT where we book the shifts and is on that form I'll like to exclude a worker from a combo when trying to book a shift and that worker is banned from that Client. Sorry Adam it was not BannedT it's ComplaintT

	Julian Okolonji OP @Reply 5 years ago

	Julian Okolonji OP @Reply 5 years ago
I want a worker not to appear in the Workercombo when try to book a shift for a client he/she is banned.

	Adam Schwanz @Reply 5 years ago
See if this helps you. Note the RIGHT JOIN and calling for what client it is in the query. You'll need to replace your fields as neccessary but this gives me any workers who do not have a record with that client. Then I just need to set this query as the rowsource of that combo.

	Adam Schwanz @Reply 5 years ago

	Julian Okolonji OP @Reply 5 years ago
Scott, Adam & Richard, many thanks to you all, everything working as intended. Keep up the good works

	Scott Axton @Reply 5 years ago
That's great news. Glad you figured it out. Thanks for letting us know.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View