Back again

[Sarah Byrne]

Right yesterday I had some problems with my relationships and tables, today I solved the issue with a lot of help but now for some reason im having a similar issue that I have previously solved but cant for the life of me figure out how I did it. I know its something to do with the relationships but don't know what it is. I am trying to add information in my JobSheets form to create a report, but every time I do this It comes up with this error code. Please see attached.

Any help is much appreciated

 

[Cronk]

I would have thought the error message was very clear. You need to have a record in tblContact before you can add a new Job record. Or remove the direct relationship between the two tables.

 

[Sarah Byrne]

I tried that, I don't want to have to add the information in a table I want the user to be able to add it into the form. And even if you add it into the contact form it comes up the same thing. Without the relationship it wont work surely?

Thanks for your reply.

 

[Cronk]

Where is the user "able to add it into the form"? You need to have a combo box with the row source based on tblContact and the bound value OpenedBy in your tblJobSheet. Also if you want it this way, code to check if the OpenedBy value does not exits in tblContact, and the functionality to add the new entry.

 

[Sarah Byrne]

Basically, each jobsheet has to be Assigned to the person who is completing the job, and who opened up the job sheet. I had put in the relationship so that this would work and it should have just been a case of saying that ID1 opened that job and the work had been assigned to ID2. The table is there the form is there, why is this not working. And as far as code goes :banghead::banghead::banghead::banghead:

Thanks again for your reply, ive been banging my head against a wall since Friday.

 

[Sarah Byrne]

I have attached the tblContacts in datasheet view. I have a join table so each contact can open many jobsheets and each jobsheet can be assigned to many contacts. Is it something to do with this? As I thought that as long as the relationship was there and it was correct that this would be it. My Relationships haven't changed other then I have done a one to many relationship with the assigned to.

 

[Cronk]

How many contacts are associated with each job. Your post #5 to me implies 2 contacts, opener and closer. Your post #6 refers to multiple contacts.

If the former, you don't need a join table, just have two fields containing the contactID for the opener and closer. (And I would call these fields ContactID_Open and ContactID_Close.) In this case you need two combo boxes to select the respective contacts. Your form shown in the screen shot in your earlier post shows neither.

If the latter, you will need to have the Job form containing a subform with all of the multiple contacts, each chosen via a combo box. You could have tick boxes indicating which is the opener, closer or whatever. You don't need a field in your tblJobSheet with the openedBy information. This way you can have any number of contacts associated with a Jobsheet.

In proper relational design, there should be a join table. However, if I knew there would never ever ever be more than two contacts, I would keep it simple and have the two fields in the Jobsheet table. I accept some old hands will argue with my "keep it simple" approach.

And either way, you still need the functionality to add new contacts. This can be done either before a new Jobsheet record is added or through the NotInList vba event.

 

[Sarah Byrne]

At the moment it is just one as its just test data. But when I finish adding them there will be about 20. Only one person will open a jobsheet but there could be 10 people working on that job. If I add a sub form tho It will show everyone whos been assigned to that job, and there may be 100 times that we go back and do work for that job so I only need it to show up for the work that we do on that job at that one time. If that makes any sense. Can this be done with a subform? I am rather new to access so im still trying to get my head around it all. Why none of it has been done with code yet

 

[Cronk]

Post a copy of your database with the JobSheet form and the tables tblJobSheet and tblContacts with some test data - that is the .accdb, not screen shots. I'll show you how the sub form should look.

You will only need code if you want to add to tblContacts when you are in the process of entering a new Job Sheet. If tblContacts has been pre-populated with all possible contacts, it is not an issue.

 

[Sarah Byrne]

How do I do that? Sorry im new to access and new to this forum. Took me 10 mins to find out how to get a picture under 100KB

 

[Cronk]

I've never attached a file on this site before so I'm guessing. I note that in the Additional Options panel below, there is a button Manage Attachments. That would seem the way to go.

When you understand the process, you will see how simple it is, but I know it seems so ponderous in the meantime. Persevere and you will get there.

 

[Sarah Byrne]

Don't know how to add just a page or two, compressed the file and its still 2.26mb. That manage attachments button just brings up the attachment options.

 

[Sarah Byrne]

Ok Attached it in zip, that was a head ache. I have taken out any sensitive information. Before you do look at it, remember I am a noob at this and this is my first one Be nice and thank you again for your help.

 

[Sarah Byrne]

Hopefully that works, as I said I didn't know how to attach just one or two pages so there's the whole thing.

 

[Fran Lombard]

Hi Sarah,

I took a quick look at your database, relationship and forms.
I came up with a couple of issues why you could not add a JobSheet Record through your form.

frmJobSheet
1. Primary reason for not being able to add a record was your form did not have a field for OpenedBy - while the referential integrity imposed by the relationship between Table tblJobSheet and tblContact required a valid reference to an existing contact.
- Add the ability to maintain this field (Can be similar to AssignedTo)

2. Your AssignedTo Combo box Row Source Type was set to Value List but should be set to Table/Query with the look-up query defined in the Row Source property

TableDesign - Relationships
1. I don't think you want a JobSheetID in tblContacts - if this was maintained a contact would be limited to one jobsheet.
2. Not sure what is going on with Parts and being assigned to Address's I would think parts would be a child to a job - but not sure of your business requirements

Hope this helps
Fran

 

[Sarah Byrne]

Thank you for your reply. My brain feels like it wants to explode today so I will put this into action Thursday when I am back into work. Il only make mistakes and delete stuff like usual when I feel like this. The parts are on the basis that they will be associated with the job that they are on (Addresses) will have a look and see if that is what I needed but I think that is right. Not sure I need a many to many any more as I have added a serial no which would mean that there is only one.

Thanks again.

 

[Cronk]

In your post #8, you said you wanted multiple contacts associated with a Job. Accordingly your tblJobSheet does not need to contain ContactID fields for OpenedBy and AssignedTo. (There are other problems with your table structure but that's next. eg tblParts has address fields - redundant.)

The attachment shows a sub form in your frmJobSheet where you can add any number of contacts.

Also look at the changes to table relationships.

 

[Sarah Byrne]

Thank you thank you thank you thank you!!!!!!!!! You did in however long that ive been trying to do in about 2 weeks!!! Since you got all the information to actually go into the form like it should do, I printed it off and then found out that he wants it to look a bit different and have information on it that I didn't have. I have changed the report and now it comes up blank. How did you get that to work? I found that as soon as I base it on a query it wont show up the information. What am I doing wrong?

 

[Sarah Byrne]

stupidly I have just deleted this by accident so I wont be able to show you what I mean. Basically I based the query from the actual job sheet upon,

tbljobsheet
tblContacts
tblAddress

So that I could add in the site name, address and the company name and numbers. This has always been the way. Is there a box that I am meant to tick or something that I am missing.

 

[Sarah Byrne]

Right I have uploaded a copy of your copy. This is what I want the job sheet to look like. I have added a lot on the query but purley because this was meant to be part of it. I am aware that the list of brief description, assigned to and comments will just bring up a duplicate over and over but this is what I want the end product to look like but with different descriptions. I could do this as a table/form and add a subtable but Im not sure as of yet. The main thing is getting the information on the report. As I said in the previous posts. I can get the information on the report from Cronks magnificent help but as soon as I base it on a query I get nothing :banghead:I am aiming to make another 2 reports to finalise this but cant do it without making a new table with everything in it and it not being normalised or basing it on a query. HELLLLPPP ME!!!!!!