Error

[Sarah Byrne]

I am yet again stuck with access. I have done all my tables done my forms made my main form with all my little forms at the click of a button, all my information in my job form turns up, all my addresses in my address form turns up but if I try and add a new job, address, part information or anything else it wont let me and now its annoying me. It just comes up with an error "You cannot add or change a record because a related record is required in table 'tblJob'". What have I done and how do I fix it?

When I do manage to add information in my forms I would like it to show up in each job as a list as well for anyone who would like to help with that. I have been told by a couple of people to add in a sub form but didn't quite like the way that this worked. Is there not any other way that I can do this?

I have made the relationship many to many for the "each job has many parts" "each part can have many jobs" so the information is there its just a case of making the information be accessible, addable and changeable.

Also I have made a report which again should theoretically add any of the information that I have been able to add and yet it all comes up blank. (Sorry I know this should be in reports)

HELPPPPP!!!

 

[EternalMyrtle]

It sounds like you are trying to add a record to a child table before adding the record in the main table. This is why we use subforms...

Other than that, it is hard to say what is going on since there are no tangible details. We have no idea what your table structure is or what the record sources of your forms are. You aren't giving us anything to go off of here.

I have been told by a couple of people to add in a sub form but didn't quite like the way that this worked. Is there not any other way that I can do this?

As far as this goes, subforms are a mainstay of Access and any decent front-end will make use of them. Not sure what your resistance is but we cannot help without more details.

 

[Sarah Byrne]

So that I can be of best help to you so that you are of best help to me what information would you like?

 

[EternalMyrtle]

Well, I think the source of the error is pretty clear even without details: you likely need a subform...

If you set up a main form/subform you shouldn't be getting this error.

 

[Sarah Byrne]

Sub form for my job sheets form or for my report "job sheets"? I Learnt what I no of this in about 5 hours worth of training in 2 weeks and think what I am trying to do is a bit beyond me tbh but there is always trial and error.

 

[EternalMyrtle]

Your form since that is where data is being entered and the error is happening.

Why don't you like subforms??

 

[Sarah Byrne]

I don't know how to use them properly and because of that they look messy When I said I was new I didn't quite alliterate the noob bit

 

[EternalMyrtle]

Got it. Well, if you show your table setup for the relevant tables and explain what you want your forms to do I will try to help.

It takes a bit of work but it is possible to make them disappear. Many of my subforms don't even look like subforms at all. No one would know.

 

[Sarah Byrne]

Right this is going to be rather long winded then as my zip is not working and the pdf is too large.

tblAddress
AddressID (PK)
AddressType (Combo box)
AddressLine1
AddressLine2
Town
County
Postcode

tblContact
ContactID (PK)
Company
Titel (Combo box)
FirstName
LastName
Email
JobTitle
Department
Phone
Mobile
Fax
Notes
Attachment

tblContactAddress
ContactAddressID (PK)
ContactID (FK)
AddressID (FK)

tblJob
JobID (PK) (Sage Code)
SiteName
AddressID (FK)
JobType (Combo box)
Comments
RelatedIssues
PartID (FK)
Manufacturer
Model
Colour
PartType (Combo box)
Size
Install Date
SerialNo


tblJobAddress
JobAddressID (PK)
JobID (FK) (Sage Code)
AddressID (FK)

tblJobPart
JobPartID (PK)
JobID (FK) (Sage Code)
PartID (FK)

tblJobSheet
JobSheetID (PK)
JobID (FK) (Sage Code)
OpenedBy
AssignedTo
OpenedDate
Status (Combo box)
Priority (Combo box)
BriefDescription
DueDate
Attachments
Comments

tblJobSheetContact
JobSheetContactID (PK)
JobSheetID (FK)
ContactID (FK)

tblPart (Added this to tblJob as it was annoying me that it wouldn't do what I wanted it to do
PartID (PK)
PartInfoID (FK)
Installdate
Comments
SerialNo
AddressID (FK)

tblPartInfo (Added this to tblJob as it was annoying me that it wouldn't do what I wanted it to do
PartInfoID (PK)
Manufacturer
Model
Colour
PartType
Size

What my aim is that you can add in all the addresses and all of our job numbers and details and you can also add in all the parts that we have used on that job. When you go onto my main page you can access the job form which comes up with all the information needed. I want to be able to add new jobs which I have added a button to do this but it doesn't want to work. I also want to be able to put part details. For example: Bosch Boiler, Blue, 20L, Serial No 35435736 Model No 900i bla bla bla. and for this to come up on the job, but then if I want to add a new part to this I can it will come up under it so that you have the boiler and a valve and so on. But for the parts, as they come up often, I want to be able to say that Job 6789 had a part no 1,2,3,4,5.... to save him time typing this up for each part. He also wants to be able to have a report that has all of this information on it at the click of a button that will pdf it and then he can email it. I have the email to pdf but have to save it via the email. As I said this is long winded. The relationships as far as Im aware are fine.

Hope this helps you help me

 

[Sarah Byrne]

Sorry there are query's as well but they also annoyed me.

 

[Sarah Byrne]

And thank you so much for your time helping me. I think the computer would be in a million pieces by now due to mass explosions.

 

[EternalMyrtle]

Ha ha, I am feeling overwhelmed by all of this so I can only imagine how you are feeling.

This is a big project and you are trying to accomplish a lot. It is not going to happen overnight. My best advice is to break it down into small pieces. You can achieve all of this but not all at once.

You should keep something closer to your initial structure not adding the Part and PartInfo fields into tblJobs. It makes sense to keep that separate. Jobs are jobs parts are parts.

What are these fields doing in the parts table?:

Installdate
Comments
SerialNo
AddressID

The serial # should probably be in parts details. The install date, comments should be in Jobs (Jobs is the main table and there will be one job many parts so a one to many relationship on the parts table). What is the AddressID? The address of the job or something else? Also should probably be in jobs.

What kind of data needs to be stored in parts? Types of parts or the info about individual parts? PartsInfo should store the model, make, serial number and other specifics right? Parts should maybe be just for generic categories of parts...

Your tables should contain data for one discrete function.

This is a fairly complicated structure with a lot of tables. I would suggest that you work hard on getting the tables absolutely solid before moving forward.

Can you show a picture of your table relationships?

 

[EternalMyrtle]

Ha ha, I am feeling overwhelmed by all of this so I can only imagine how you are feeling.

This is a big project and you are trying to accomplish a lot. It is not going to happen overnight. My best advice is to break it down into small pieces. You can achieve all of this but not all at once.

You should keep something closer to your initial structure not adding the Part and PartInfo fields into tblJobs. It makes sense to keep that separate. Jobs are jobs parts are parts.

What are these fields doing in the parts table?:

Installdate
Comments
SerialNo
AddressID

The serial # should probably be in parts details. The install date, comments should be in Jobs (Jobs is the main table and there will be one job many parts so a one to many relationship on the parts table). What is the AddressID? The address of the job or something else? Also should probably be in jobs.

What kind of data needs to be stored in parts? Types of parts or the info about individual parts? PartsInfo should store the model, make, serial number and other specifics right? Parts should maybe be just for generic categories of parts...

Your tables should contain data for one discrete function.

This is a fairly complicated structure with a lot of tables. I would suggest that you work hard on getting the tables absolutely solid before moving forward.

Can you show a picture of your table relationships?

 

[Sarah Byrne]

I know, I know I did say I think I had bitten off a bit more then I could chew. I am off tomorrow and back on Thursday and will change the bits you have suggested. This is going to make this difficult as its going to mess up what I have already got but if what I have doesn't work then it doesn't really make that much of a difference I am new to this forum and have no idea how to add things other then the attach bar at the top but its very limited to the size and the size of the file that I am trying to add is bigger then it limit. Email?

 

[EternalMyrtle]

It look pretty good though so don't get too down. To attach, click on the "Go Advanced" button and find the "manage attachments" button. Then just select your file(s).

If you post in the table section and show your relationships, you will likely get tons of helpful advice.

 

[Sarah Byrne]

May have attached the file.

 

[EternalMyrtle]

Also better to change the structure now rather than further down the road when it becomes much harder. Will take a look when I can and let you know what I see. Have a meeting to get to now.

Hang in there

 

[jdraw]

I would suggest you do not use lookups at the table field level.

blAddress
AddressID (PK)
AddressType (Combo box)<=========
AddressLine1
AddressLine2
Town
County
Postcode

tblContact
ContactID (PK)
Company
Titel (Combo box)<========
FirstName
LastName
Email
JobTitle
Department

Make these true separate lookup/validation tables.

AddressTypeLkup
AddressTypeCode PK
AddressTypeName

TitelLkup
TitelId PK
TitelName

 

[EternalMyrtle]

All in all, your relationships seem good.

You should follow Jdraw's suggestion and remove the table level lookups and set the data types for the FK fields to number. As I mentioned before, you might post in the table section for more responses.

So on to the forms.... What is the record source of your main form and what exactly do you hope to achieve? Now that I know the relationships, explaining the form/subform situation should be easier.

 

[Sarah Byrne]

I want to achieve, telling the computer to do something and it do it . Basically in a nut shell the final outcome is to have a piece of paper that has all of each jobs details on it. So that you can pull out a file and no exactly what is going on in the job. If he needs to order a new part for a job that happened 2 years ago he can find it with a click of a button. But to do all of this I need to be able to input information and it stay there and add to the other information. And for my report to show all the information that it should rather then either blank or #Name? or #Error! both of which are rather annoying when from what you can see it is correct. But as I said I am a noob and have bitten off a rather large bite. Ill post this in the tables forum and hope for help .

Thanks again.