Solved

Form will not create a new record

Posted on 2013-05-16
14
373 Views
Last Modified: 2013-05-23
Dear experts
Hope you can help me with this little problem.

I have attached a db to show the problem.

Go to the Switchboard, select Maintenance then Groups then New group.

If you enter data into this form and click Save it does not save the record.

The Edit function uses basically the same form and that works fine.

I feel that it is related to the query behind the form because if I change the data source to simply tblGroups, it will create a record.

Please can you give me some idea what is wrong.

Best regards
Richard
Minibus-database---v0.1.mdb
0
Comment
Question by:rltomalin
  • 7
  • 6
14 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39171058
The problem, as you noted is that the query behind your form is not updateable.

A better (and working) way to structure your form would be to create subforms to handle the contact detail information.

The mainform should be bound to tblGroups, and the subforms to your contacts table.

The mainform and subforms should be linked by contactID

Take a look at this tutorial for details:

http://office.microsoft.com/en-us/access-help/create-a-form-that-contains-a-subform-a-one-to-many-form-HA010098674.aspx

The example in the article uses continuous forms to show multiple detail records, but in your case you can also show your subforms in single form view.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39171077
Another way to handle it (this is the method I'd use) would be:

- Use tblGroups as the recordsource for your main form
- use a single ContactID field in tblGroups for all three types of contacts
- add a contactType field to tblContacts to indicate whether a contact is Main, Invoice, Emergency (or anything else)
- add a single subform to your mainform, linked to the parent form by GroupID, with a recordsource of tblContacts
- Display the subform in continuous forms or datasheet view and include ContactType as a visible field

This method will allow you to easily expand the number and type of contacts.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39171107
Another suggestion - IF any of your contacts can possibly be included in multiple groups or multiple roles, you would be better off with three tables:

1.  tblContacts - stores basic information about individuals (ContactID, FirstName, LastName, Address, etc)
2.  tblGroups - stores your group information  (PK GroupID, GroupName, etc)
3.  tblGroupContacts - Contains an autonumber PK field,  groupID, ContactID, GroupContactType and any other info describing this group/contact combination, and links your groups and contacts together
0
 

Author Comment

by:rltomalin
ID: 39171146
Thanks for the tips.  Will have to look at this later now, but I will get back to you.

Regards

Richard
0
 

Author Comment

by:rltomalin
ID: 39174827
Hi mbizup

I am about to look at this again.  I will probably use your first suggestion.  Can I please just clarify the following points:
I create three new forms to use as subforms.
I will need 3 new queries for these forms (1 query for each subform)
The subform queries will have a filter that relates the relevant contactID in the main form to the ContactID in the subform (eg [Forms]![frmNewGroup]![MainContactID])
I also don't quite understand why the frmNewGroup will not work, but the frmEditGroup seems to update the group record fine.  If I need to change this form as well I will have to create 3 more subforms and 3 more subform queries (although maybe I could re-use)

Could you explain that please.

Best regards

Richard
0
 

Author Comment

by:rltomalin
ID: 39180769
Hi mbizup
I have created a subform to display the contact details (just for one contact at the moment)  But I cannot seem to get it to pick up the data.

In the attached sample db, if you simply open up frmNewGroup, you will see that the contact details are displayed.  But if you change the contact in the combo box it is not picked up by the subform.  It only ever shows the details that were there when the form is opened.

Can you please take a look and suggest what I have done wrong.

Best regards

Richard
Minibus-database---v0.1.mdb
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39180791
Hi Richard,

Sorry for the delay here.  I will try to get to this later today.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 61

Expert Comment

by:mbizup
ID: 39182425
A few issues...

1.  The rowsource of your 'add' form's contact combo list is linked to the mainform by Group ID.  At this point, you have not necessarily  saved your new group, so the combo should not be using group ID as criteria

2.  The JOINS in the record source of your Edit Form should be LEFT JOINs (although I'd recommend using the same subform approach as in the Add form)

3.  Not really an issue, but the combo boxes are displaying IDs.  It's possible to display the full name instead while storing the ID.  Take a look at the changes to the Rowsource query, and the column width property.

See if this works:
Minibus-database---v0.1.mdb
0
 

Author Comment

by:rltomalin
ID: 39182446
Thanks mbizup

I'll check this out at work tomorrow.

Regards

Richard
0
 

Author Comment

by:rltomalin
ID: 39183744
Hi mbizup
Thanks for your suggestions.  That doesn't quite do it though.  Your code for the combo box just shows all the contacts, not just the ones for the group.

For the New form, the combo box should always be blank to start with (because there can't be a contact because the group doesn't exist yet.)

So the user would use the option to create a new contact from the form, then select that.  For the other two contact fields the user could select the contact just created and/or create a new one.

I can see your point about the Group not actually existing for the combo box to use.  Would it be possible to save the record when exiting from the Group name field?  

Sorry I am a bit stumped on this.

Regards

Richard
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39188781
That makes sense...


- We can add the group filtering back into your combo box
- In addition to your ContactID, your subform needs to be linked by GroupID.
- In the popup form for adding new contacts, you need to set the combo to the id of the newly added contact, and then requery the subform to display the details. (See the SAVE button code in the popup form)

Try this:
Minibus-database---v0.1.mdb
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39188849
If you put

dirty=false

in frmnewgroup / Save_Click before docmd.close, then, when you press the button, you will see the error message that prevents from saving.

You probably have some related record elsewhere, and need to specify something in the dropdowns before saving, but they are empty.
0
 

Author Comment

by:rltomalin
ID: 39188928
Thanks for the feedback, I will take a look at this tomorrow morning.

Regards

Richard
0
 

Author Closing Comment

by:rltomalin
ID: 39190335
That's fine now I think.  Thank you mbizup.

There may be a delay now before I get back to this in details, but I am sure I am on the right track.

If I have further questions I will raise another question.

Regards

Richard
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now