Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Form will not create a new record

Posted on 2013-05-16
14
Medium Priority
?
406 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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
 
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 2000 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

PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

721 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