Subforms

[GinaWhipp]

Question...

Why is CustomerID in the Employee table?

 

[AndyPandy]

Thanks i dont know as it is where i thought is should be my thinking was this

one customer can have many contacts

yes there would be more employees at any given branch but for assigning calls to engineers it would only be the engineers who do jobs so it would be one branch can have many employees

I dont know about the customer id in emplyee table lol

 

[GinaWhipp]

More questions...

Isn't Job Details tied to a Call?
If you want Notes to go to History the minute they are entered then use an UNBOUND Text Box and run an APPEND query but trying to APPEND them after the person is finsihed will not only cause Write Errors but will most likely lead to corruption. AND, I would push History out to another table but that's up to you becuase I wouldn't store Notes this way.

The way you hace Customer Name and Customer Addresses linked will never work. Customer Name should be the primary.

 

[GinaWhipp]

Okay, adjust your file and sending it back. Hopefully, this will help...

 

[AndyPandy]

so what or where would junction table go and where would the forigen keys go.

 

[GinaWhipp]

Did you download my sample? If not, please do and look in the Relationship Window.

 

[AndyPandy]

sorry ignore last message noticed that customer table is only linked to names would it not be linked to jobs as well as one customer can have many jobs/cases will this now allow me to do the form desighn i am after, also hpw do i do them selection optopn and lnk them to job type,

 

[GinaWhipp]

Oops, forgot, it links to tblCallDetails. No need to link to tblJobDetails because tblJobDetails is linked to tblCallDetails and the Customer ID is in there.

 

[AndyPandy]

Thanks for that been away for the weekend so not had chance to look at it, so with this layout I would be able to create one data entry form as such i just trued creating one for customer name and address details by address and it said data not in record set. by using form wizard

 

[GinaWhipp]

You cannot create one Form you will need to use subforms but they do not have to be Tab Controls so no one will know they are subforms. In other words, it will look like one form.

 

[AndyPandy]

You cannot create one Form you will need to use subforms but they do not have to be Tab Controls so no one will know they are subforms. In other words, it will look like one form.

I know what you mean, I have started to create the forms please see attached however my next form tbl job details i am having trouble with,

when you select who the engineer to assign it to it does not auto display branch how can i do this.
or the engineers telephone number

also when i select engineer i want that field to display engineeridnumber forname surname

 

[GinaWhipp]

Isn't Branch attached to the Employee? Why is it in the tblCallDetails?

In any event you need to a taxt box on your form and use a DLookup(), that would be the easiest way. The same for the Engineer... use a DLookup().

 

[AndyPandy]

Isn't Branch attached to the Employee? Why is it in the tblCallDetails?

In any event you need to a taxt box on your form and use a DLookup(), that would be the easiest way. The same for the Engineer... use a DLookup().

Hi it is in table call details because thats the design of the tables that you had created but i will delete it.

never used dlookup in access know bit about lookups as in lookupwizard that about it.

 

[GinaWhipp]

Hmm, that was a mistake, it's attached to Employee's it can come out. Here's a sample of a DLookup()...

If ID field is numeric...

=DLookup("FieldFromTableOrQuery", "YourTableOrQuery", "[FieldFromTableOrQuery]=" & Me![FieldFromForm])

If ID field is text…

=DLookup("FieldFromTableOrQuery", "YourTable", "[FieldFromTableOrQuery]='" & Me![FieldFromForm] & "'")

OR
If you use a Combo Box…

=[ComboNameHere].[Column](PutColumnNumberHere)

…and remember Combo Boxes start with 0 not 1.

 

[AndyPandy]

THANKS i AM ALSO TRYING TO GET HISTORY FIELD TO WORK.

ma trying to create a history field where by I enter the notes and then the notes appear in history with a time and date stamp, i belive it has something to do with =coloum history expression. the form is called job details and it will link up with the main form as a sub form for one form for data entry.

I am also trying to create a form that allows me to assign employees to branches can you help with that

 

[AndyPandy]

Write I shall try again

I have created a main form that contains customer name and address then a subfrm that holds the call type and the operater details, the next subform i am trying to create is one that holds the job details who dispatched it and notes.


1. it should store the job details or each customer as long with all the noted for that customer and that specific job linked to main form sub form 1 and main form
2. what happens is is says unable to update field, i have experiemented with different forms as well by removing the jusntion table and this seems to work, however whenever i have add the notes to the notes section saved it and then gone back into that same recod the notes i entered have now dissapeared.

can anyone help resolve this please

Then this form needs to be linked with the main form.

 

[GinaWhipp]

If the notes are in a seperate table they need their own subform. Remember, you can't join all your tables together to make the form it will render the query unupdateable which, obviously, makes the form useless. You also do not need to bind the Customer table to a form to have the information show. There are several ways to do this without binding it to the main form.