UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Can't Add New Record In Form, Access 2016    
 
   
StaceyG1981
post Jul 16 2017, 09:13 AM
Post#1



Posts: 29
Joined: 4-February 17



I have a form combining 3 tables. There are 3 records in the main form (ProjectOverview) and links to 2 other tables (1 to many relationship).

the subform (PalletSheets) has 20 records but they are grouped by jobcode so only show the related records in each of the 3 (e.g. 1 record has 18 palletsheets and the other 2 have 1 each).

It won't let me add a 4th record in the main form view unless I remove the group by in query - but then it shows a record for each palletsheet in the projectoverview which I don't want.

I've gone through all the checks I've read online (about data entry etc). My relationship shows 1-many with jobcode in projectoverview as the primary key

The only thing I can do to make it let me add new records is remove the 'groupby' in the form query

Any ideas where I have gone wrong please?

Thanks

Go to the top of the page
 
theDBguy
post Jul 16 2017, 10:09 AM
Post#2


Access Wiki and Forums Moderator
Posts: 70,270
Joined: 19-June 07
From: SunnySandyEggo


Hi,

As you already found out, using a Group By query will make the records "read-only." The main form should be based on the parent table and the subform should be based on the child table. If you want to filter what to show on each form, try applying a criteria to your query. Alternatively, consider posting a copy of your database with test data, so we can help you figure out a better way to design your forms.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
MadPiet
post Jul 16 2017, 10:09 AM
Post#3



Posts: 2,038
Joined: 27-February 09



Totals queries are not editable. Remove the GROUP BY
Go to the top of the page
 
StaceyG1981
post Jul 16 2017, 10:30 AM
Post#4



Posts: 29
Joined: 4-February 17



I have removed the GROUP BY feature so I can add new records but as it's showing all the ones from the subform, how do I check that I have the subform based on the child table?

How can I tell which is the parent table? I have relationship linking the projectoverview primary key (job code) to the palletsheet table as 1 to many but is that enough/correct?

I'm making it up as I go along! ha!

Thanks
Go to the top of the page
 
tina t
post Jul 16 2017, 11:06 AM
Post#5



Posts: 5,051
Joined: 11-November 10
From: SoCal, USA


QUOTE
How can I tell which is the parent table?
...
I'm making it up as I go along!

you're going to continue to suffer a lot of frustration in trying to build and use your database, unless you invest the time now to learn the basics of relational design principles. my best recommendation is that you STOP working on your database entirely, close Access, and study relational design. there are links here at UA to many resources, and you can find more just by googling, and many books, available new, used and e-book, that teach relational design principles, and process analysis.

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
theDBguy
post Jul 16 2017, 01:41 PM
Post#6


Access Wiki and Forums Moderator
Posts: 70,270
Joined: 19-June 07
From: SunnySandyEggo


Hi,

As I had suggested earlier, you might consider posting a copy of your database with test data, so we can give you an idea if your design is going in the right direction.

Just a thought...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
MadPiet
post Jul 16 2017, 02:42 PM
Post#7



Posts: 2,038
Joined: 27-February 09



I agree.

Please explain what your database is going to keep track of/store information about. If you can explain that, someone here can help you sort it out. Those first databases, before you get your head around the relational stuff, are really hard. I would build the tables first with only a few records. Then make sure the design is okay by writing some test queries to proof the design. Then you can build forms etc. If you start somewhere else, you may find you've spent a lot of time on a design that just plain doesn't work.

Write it down and draw pictures if you need to. (Whiteboards are ideal for that). Post what you have and we'll try to help.
Go to the top of the page
 
StaceyG1981
post Jul 20 2017, 02:18 PM
Post#8



Posts: 29
Joined: 4-February 17



Hi

I have attached the database - relevant objects in the top section.

I can add new records now so the problem is the project overview is showing all records from the pallet sheet overview (18) instead of the 4 unique records based on job code.


I expect you'll find a whole heap of mistakes! I'm not a developer or anything similar (I'm just a PA) - I'm just doing it for internal use for my husband's job (doing in my spare time as a favour and massively making it up as I go along)!


Thanks for your help!

Attached File(s)
Attached File  WMD_test.zip ( 84.73K )Number of downloads: 0
Attached File  relationships.JPG ( 65.18K )Number of downloads: 2
 
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th July 2017 - 03:39 AM