My split form won't save new entries.

[TazoTazmaniac]

I am fairly new to Access and am trying to create a database that stores client details, sets up appointments and keeps a record on stock, amongst other things. I have been searching Google to no avail to find an answer to the issue that I'm currently having so am posting another question here. Please help!

Basically, I have a form that contains most of the contact details for someone using the service, and inside that there is a subform containing the address. When a client wants to change their address, there is a button to add a new address. Now this button runs a macro which does three things; firstly, it opens a new form to add a new address, then it goes to a new record and then it sets the value for first name and last name as the name of the person for whom we want to change the address.

The new form that is now up is based on a query to only bring up the details for the client on the open form for all the client details. It is also a split form; so basically all the old addresses are listed in a table at the bottom and the top of the form is where I can enter a new address (so has all the address fields, the address status [primary or inactive] and their first and last name).

What I've then done is add a button that changes any old addresses to inactive and the new address entered to the primary address. All this works until I want to save the record and exit the form to go back to the original client details. The data is not saved and it has disappeared from addresses. I have tried putting into a macro a save record command, which didn't seem to do anything, and also tried coding it in with the code below:

If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
End If

Most of the things I've tried just close the form without saving the new address I've put in, but once the pop up withdow stating "The Command or Action 'SaveRecord' isn't available now" came up.

What have I done wrong/what can I do to fix this? I am really stumped and have spent a few hours trying to work it out so any help would be appreciated. Thank you in advance.

[rpeare]

can you supply a copy of your database with fake data in it for analysis. It sounds like you have a bad structure to start with but it's hard to tell on the details listed. Also do you mean form/subform or do you really mean a split form? Realistically you should have a table with your customer information that contains things that don't change, or change very very infrequently (first name last name, etc) you should have separate subtables for other things that may change over time that you also want to track (i.e. an address table that has the customer ID as the foreign key and the address information). Without seeing your actual structure I'm not sure I could say more than this.

[Missinglinq]

The "The Command or Action 'SaveRecord' isn't available now" message makes it seem likely that the Form involved is Read-Only. To be honest, I'm having a hard time following your explanation of these Forms, but if the Form is based on a multi-table Query, this is the likely culprit, as many of these are Read-Only. For the reasons why have a look at Allen Browne's article on the subject:

Why is my query read-only?

Linq ;0)>

[TazoTazmaniac]

Thank you for your answers-i haven't got access to the database until Monday now, but will attach it then to hopefully make it more clear. Be aware though that's it's certainly a work in progress!

Rpeare, it's definitely a splitform, not a subform that I mean. I also already have an address table separate to the rest of the details. The split form was so that when I need to add a new address, I could also see the old ones at the same time, but I hope it'll become clear by Monday when I can attach the database.

Missinglinq, I haven't looked into that possibility so it looks hopeful. Again, I can look into it on Monday.

Thank you for your patience :-)

[TazoTazmaniac]

Thanks for waiting for me to get back to the database today. After looking at the problem with fresh eyes, I have found that the records (i.e. the addresses) HAVE been saving, but the issue is that they've been creating a new client ID number (the foreign key) for each one. So what is happening now is with very new address being added, it puts in a new address ID (which I want) but also a new client ID (which I don't want) so in reality there's a load of new addresses that are floating around and not applying to any clients at all. How do I stop it creating a new foreign key every time and copy the foreign key from the open form already open?

I've attached a sample of the database with only the parts which I'm talking about (with connected items). Hopefully it may help to make what I'm saying clearer.

Thank you!

Client, Stock and Appointment Database_ClientDetails.zip

[rpeare]

If you're talking about the form frmClientDonorContact that's a subform, not a split form (I'm assuming it is because you mention multiple addresses and this form has that capability. The query driving the subform does not need the table tblClientDonorContact at all, since the foreign key is the contact ID and and the form/subform are correctly linked. I removed the table tblClientDonorContact from the query driving the subform frmAddressesSub and entered a new person, then entered a couple of addresses and the correct contact ID was populated in the table tbladdresses.

[TazoTazmaniac]

If you're talking about the form frmClientDonorContact that's a subform, not a split form (I'm assuming it is because you mention multiple addresses and this form has that capability.

Thanks for your reply, it is much appreciated. The split form I was talking about actually was frmAddNewAddress, which is the way I want the new addresses added (I will eventually be taking the navigation(?) bar off the subform in frmClientDonorContact. The split form's the one I can't get to work. Looking at it today, I thought it might be because the foreign key (ClientID from tblClientDonorContact) is an autonumber as well as the AddressID, but when I changed it to the linked ClientID in tblAddresses, I got this error code 'The current field must match the join key '?' in the table that serves as the 'one' side of one-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many-only' table.' which has now made me all the more confused! This one has definitely got me. Any idea?

[rpeare]

There are all kinds of issues with the way you are setting this up.

1. your form frmAddNewAddress is using a query that does not need the table tblClientDonorContact and is not updateable (in terms of inheriting the contactID)
2. your form frmAddNewAddress will not work the way you have it set up, in order to inherit the contactID on BOUND forms you would HAVE to use a form/subform arrangement to inherit the FK (foreign key) to the 'master' table (tblClientDonorContact) OR you would have to use VBA to populate the contactID. You have studiously avoided vba in this application so I am assuming you're not terribly comfortable with it
3. You can not use (I don't think someone else can correct me if I'm wrong) a split form as a subform which sort of blows the functionality you're looking for out of the water.
4. You are splitting a simple form into three different forms unnecessarily. I'm enclosing a copy of your database back to you with very little code attached to the form frmClientDonorContact.

Client, Stock and Appointment Database_ClientDetails.zip

I'm using a simple form/subform arrangement. If you want to get fancy you can have the subform use a list box the same way the main form does. but there's no reason to split your forms up as radically as you have.

[TazoTazmaniac]

rpeare, thank you for putting the time into this for me. I really appreciate it.

I now understand why the form I originally was trying to do didn't work. As I was completely new to access when I first started creating this database, I've been learning as I've been going along. I have used quite a bit more VBA in the later parts of the database (there would have been barely any of it in the part I've sent you), but am still trying to come to grips with it! So it's less 'avoiding' VBA and more not knowing where best to use it!

What you sent back was very helpful (thank you again) and what I've now done is edited it so that the subform now uses a list box as you suggested. It would, however, still be helpful to have a pop up box (form) where I can add a new address so I can make the addresses in the list on the main form uneditable. The aim for this database eventually will be that any member of staff could come in and use the database without any knowledge of access and with minimal training and there would be minimal opportunity to accidentally edit anything. Do you have any idea how I could add that in? I don't mind using some code at all if I need to.

It's good to have fresh eyes on something I've been looking at a lot so thanks again

[rpeare]

if you want to prevent people from changing information, don't use bound forms at all, or alternately lock/disable the fields you do not want people to change.

There is no address information on the main form so I am not sure to what you're referring the example I sent you has client information on the main form and address information on the subform. You can 'lock down' the information on the subform similar to the way I've done it on the main form by using a list box and only bringing up a specific record and, say, if they click on an existing address you can disable the controls in the subform so they can't change an existing record, they can only add new information, however this would mean that if they typed something in wrong at the start they wouldn't be able to edit it.

[TazoTazmaniac]

Sorry for the delay in replying - work got busy with everything so didn't get a proper chance to look at this. I don't think I was very clear in my last post so I'll try and explain myself again, although I've now given up on a pop up form! So I've locked down the controls by doing a list box like you suggested originally, and I've also put in a sub form for addresses. What I want to do (with VBA I assume) is when I select one of the addresses in the list box, the selected address comes up in the sub form. However, I've tried a few things and it doesn't work. To be honest, I've no idea how to get it to work. What code should I be looking at putting in?

[rpeare]

it's just an extension of the theme like I said, create a list box with the addresses that is updated when you choose a person, have their address information refresh, when they select something from the addresses, refresh your subform with the individual address information.

This is the simplest way I could think of using bound forms.

Client, Stock and Appointment Database_ClientDetails.zip

[TazoTazmaniac]

rpeare, thank you so much! I couldn't for the life of me work out how to make the different addresses appear and I tried all sorts of coding from frmClientDonorContact. What I'd missed was the record source on the form used as the sub form - I had just put in tblAddresses and that's where my problems came in.

Thank you for your patience. I'll probably be back on this forum soon with my next problem