A couple of questions... Please help (1 Viewer)

[Johnsonsimon]

Hi all,
Apologies as I have not had access to a computer for the past almost a week and have only just managed to get access now. I will have a look at the suggestions/changes made and will post my findings later.

Thank you all again for all your help I genuinely do appreciate it all.

Thanks guys

Si

 

[cplmckenzie]

johnsonsimon,

Bear in mind that no actual changes, except disabling the save record feature on the Client Form, as the record is automatically saved to the underlying table.

Also, on the Client form without the record scroll buttons, the contacts subform exist already that form also, it is just set to visible = false.

cplmckenzie

 

[Johnsonsimon]

Hi cpl,

I just posted a reply and for some reason the forum logged me out, oh well... haha.

I have had a quick browse through the amended DB, all looks pretty good to me. There are a few points I picked up on, that I presume are partly down to the only semi-altered things you have changed on the DB.

Clients Form. The sub form shows all records not just the ones for that client.

Can this subform just be shown in a table style view? but formatted to match the design of the rest of the DB ( I am happy to do the design part if you can change the basic layout).


Jobs Form. I think I had the Save button purely to refresh the page so that the subforms at the bottom of the page would be updated.

When you create a new job and select the CONTACT NAME, it doesn't appear to save this anywhere as when you click away from the record and go back to it the field is blank, or when you select a CONTACT NAME and click to the next record the same CONTACT NAME is selected.


But as regards the intended functionality, the way you have implemented it would work an absolute treat, and if the same were true for the Lead Passenger as well that would be fantastic.


I hope that makes sense and thank you again.

Cheers

Si

 

[cplmckenzie]

johnsonsimon,

Yes on Client Form the Contact subform shows all contacts from tbl_AddContact, as it is based on the table.

Therefore, when you Add A New Client ... then click Add New Contact you
can continue adding (as many) contacts for that particular Client and each added contact is written to tbl_AddContact.

Also on Form Jobs& Booking that contact number entry is merely placed into a textbox for reference info only, as there is no Contact info stored in the tbl_Job design.

As far as storing this data, refer to earlier image2 and related conversation about the creation of an additional table for this type data.

"This relationship design will permit you to create a Contact table with many contacts to one client. The modification to the Jobs and Booking Form, changing the contact textbox to a combobox populated from the Contact table Where tblContact.ClientName = Forms!ClientName.

Note that by the creation of tbl_Contacts_Job You can get the answer "Which Contacts at Client A produces the most completed jobs" and why does this particular contact have so many canceled Bookings"

So when (if) this table is created you can build a Form for viewing (not creating) Jobs & Booking based on this new table and pulling relevant data from other tables as now all these tables are linked by

tbl_Client.ClientID
tbl_AddContact
tbl_Job Number

from those respective tables.

cplmckenzie

 

[Johnsonsimon]

Hello again all,

I have not had any internet access for quite some time now, so this project has been put on the back burner, but now I am getting "pressure" from the powers that be to get it completed.

I really appreciate all the input from both Guus and cplmckenzie, but I am yet again completely stuck.

I have looked at the alterations again, after these few weeks away, that cpl made and I am at a complete loss as to where I am up to. I feel really bad for keep coming back with question after question.

If cpl or anyone can get the databse into a useable state, I can try to get back on board with the rest of the 'development'.

As discussed earlier in the thread I just need to be able to do the following;

Jobs & Booking Form
When the CLIENT is selected from a combobox (drop down box), a further drop down box is populated with the CONTACTs for that client. When selected this info needs to be saved/stored in one of the tables, I presume the Job Table would suffice.
Also on this form on the JOB DETAILS tab, I need to almost repeat the above but for LEAD PASSENGER.
These need to be editable in some way, by opening another form to be able to add CONTACTS and/or LEAD PASSENGERS for the currently selected client. It would also be handy if it was possible to just overtype what is in these boxes should an adhoc (non-regular customer, contact or lead passenger) need to be entered.

CLIENT FORM
Need some sort of subform that displays ONLY the CONTACTS for the currently selected CLIENT, this subform should also allow the editing or addition of new CONTACTS for the currently selected CLIENT.
I would also need the same functionality for LEAD PASSENGERs. Whether this is to be done with another table for passenger details or via some form of query or filter.


As per previous recommendations I plan to delete the COMPLETED and CANCELLED jobs tables and incorporate a field(s) in the Jobs table to take care of this function, and base a query on both of these that will populate the COMPLETED and CANCELLED jobs forms.

I really hope someone can help with this as my head is completely pecked.
I feel totally guilty for asking others to do some or even any of the work, if anyone can give me detailed directions on how to proceed I will undertake the work myself.

Thank you all so much again.

Si

 

[cplmckenzie]

johnsonsimone,

the attached zip file contains the originial database, uploaded by you, before any modifications.

Suggest you keep the original and the up to this point the Modified version, read through the post since May till Present to get back on track of process.

I am currently involved in 3 other projects on 2 forums at present.

Post as you get back involved, I am quite tied up with other work right now.

cplmckenzie

 

[Johnsonsimon]

Hi cpl,

No worries if you are busy. I may just strip it back to table state and go again from there. I think I might have figured out a way for doing what I want by using sub forms and a button back with a line of vba.

I will let you know how I get on.

Cheers

Si