[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

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
0
rltomalin
Asked:
rltomalin
  • 7
  • 6
1 Solution
 
mbizupCommented:
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
 
mbizupCommented:
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
 
mbizupCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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

Regards

Richard
0
 
rltomalinAuthor Commented:
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
 
rltomalinAuthor Commented:
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
 
mbizupCommented:
Hi Richard,

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

I'll check this out at work tomorrow.

Regards

Richard
0
 
rltomalinAuthor Commented:
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
 
mbizupCommented:
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
 
Vadim RappCommented:
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
 
rltomalinAuthor Commented:
Thanks for the feedback, I will take a look at this tomorrow morning.

Regards

Richard
0
 
rltomalinAuthor Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now