Solved

Form will not create a new record

Posted on 2013-05-16
14
363 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

743 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

13 Experts available now in Live!

Get 1:1 Help Now