Link to home
Start Free TrialLog in
Avatar of rltomalin
rltomalinFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Form will not create a new record

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
Avatar of mbizup
mbizup
Flag of Kazakhstan image

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.
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.
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
Avatar of rltomalin

ASKER

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

Regards

Richard
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
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
Hi Richard,

Sorry for the delay here.  I will try to get to this later today.
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
Thanks mbizup

I'll check this out at work tomorrow.

Regards

Richard
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
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Thanks for the feedback, I will take a look at this tomorrow morning.

Regards

Richard
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