Append query to make new record

[Snufflz]

I am using an append query to make a new record in my database.

The database is to list clients who have been involved in car accidents. At the moment the vehicle owner is entered into the database (table:all_records) and the passengers in the vehicle are entered into a separate table (tableassengers). These are linked through the client ID.

I have now been asked if it is possible to create a new client from each passenger record. That means copying the passenger data into the client fields and copying the rest of the accident/vehicle details from the original client record.

Very helpful advice on here suggested an append query, which I have done, via a macro button in the details section of the passenger subform.

The problem is that when I use the button it wants to append all of the passengers instead of each one individually as not all passengers will become clients. The macro on the button is simply to open the append query.

Any help with this would be appreciated.

 

[Trevor G]

Small suggestion is to add a tick box to the second table and when you show the records you want to append then set the query to look at the tick box if ticked.

That should work for you.

 

[Snufflz]

Small suggestion is to add a tick box to the second table and when you show the records you want to append then set the query to look at the tick box if ticked.

That should work for you.

Thank you for your help. I'm afraid that I don't have a clue how to set the query to look at a tick box though.

A hint would be very much appreciated.

 

[Trevor G]

OK go to your table and then select the table that will require a tick box and use the right mouse button and select Design View. From the field names that are listed go and add a new one at the end, and give it a name you understand, like AddToList and in the data type select to use Yes/No. That will add the tick box.

Then you need to show that in your form. So save and close the table.

Then select the forms and find the subform you are using, again use the right mouse button and select Design View, then you need to display the field list and drag your tick box onto the form in the correct place in the Detail Section. Then save and close.

Then you need to go to your append query and go into design view, from the table that you created the tick box you double click on its name that will add it to the list in the selected fields. You then look at the criteria and type in yes

Then safe and close, then try opening the form, go to a record, place a tick in the box against a record you want to append and run the macro to see what happens.

If this fails you can you upload the database to this forum and I will take a look, if you need to strip the data if sensitive then create a copy of the database first.

Give this a go if you can follow the instructions.

 

[Snufflz]

Many thanks for that. I knew about Yes/No fields, I just didn't get how you meant to attach it to the query.

Unfortunately it did not work and when I run the query it says that 0 records will be exported.

Typically also the database is too big to be uploaded here. Even stripping the data out I can't get it to below 3.6mb. I think I might throw it through the window instead!

 

[Trevor G]

Many thanks for that. I knew about Yes/No fields, I just didn't get how you meant to attach it to the query.

Unfortunately it did not work and when I run the query it says that 0 records will be exported.

Typically also the database is too big to be uploaded here. Even stripping the data out I can't get it to below 3.6mb. I think I might throw it through the window instead!

If you are appending why would it say 0 records will be exported? If the tick box is set and you have ticked some records then you should see something. If you look at the query from records you have set to yes or true do you see any when you switch the view?

Can you create another database just importing the tabels and query and forms you are using then zip it up and upload to the forum.

 

[Snufflz]

Stupidly, I never thought of zipping it.

There are two databases - Accident data (tables) and Accident admin (the workings).

The main tables in this instance are T:all_records and Tassengers. The append query is Q:append.

If you go into F:fault and go to the Passenger/Injury tab, you will see the list of passengers.

I have stripped out the data except for a test case.

Thank you very much for taking the time to help me.

 

[Trevor G]

Stupidly, I never thought of zipping it.

There are two databases - Accident data (tables) and Accident admin (the workings).

The main tables in this instance are T:all_records and Tassengers. The append query is Q:append.

If you go into F:fault and go to the Passenger/Injury tab, you will see the list of passengers.

I have stripped out the data except for a test case.

Thank you very much for taking the time to help me.

OK the main database you attached resides on a server so it is stating I can't do anything with it in the normal views, the second database you attached the append query looks as though it is appending records from the table to the same table that seems strange, would you have a look at creating another database that resides on your c drive or desktop, import all the objects from the first database and zip that up I can then see the form and see whats happening, I also wouldn't use the word Add as a field name, Access has many reserved words that you can't use.

I will pick this up again once you have sorted this.

 

[Snufflz]

I've sorted that and they are now just c:\. I've also changed the name of the add field to 'New record'.

Because the information about the accident applies to the passenger once he is made into a client, I have to copy the relevant information into the new record. I know it seems wrong to have repeating data but it is necessary.

Thanks again. Very much appreciated.

Regards.

Elaine.

 

[Trevor G]

I've sorted that and they are now just c:\. I've also changed the name of the add field to 'New record'.

Because the information about the accident applies to the passenger once he is made into a client, I have to copy the relevant information into the new record. I know it seems wrong to have repeating data but it is necessary.

Thanks again. Very much appreciated.

Regards.

Elaine.

Elaine, you have the tables now as linked tables, so I can't open them so the switchboard fails.

 

[Snufflz]

Sorry about that. I've stopped the switchboard starting on load and I've made the data local as well.

Many thanks.

 

[Trevor G]

OK Elaine,

I have looked at the append query as you have renamed the field it will initially show an expression criteria so you need to reselect the field by its proper name. I then selected a passanger from the form and as you have the macro to run this on the tick box it creates the record.

I wouldn't use the tick box to run this as if there are other records that have the tick in then it will also generate additional records to the t:all_records table.

I think you may have to go down the line of vba to populate the t:all_records table based on the record you have selected otherwise you will continue to get multiple records.

I will look to create some code for you a little later on today.

By the way I like the look of your database. One or two more tweeks and I am sure it will work in the way you want it to.

Kind regards

 

[Snufflz]

Trevor, you are brilliant. Thank you so much for your help. I also appreciate your comments about the database.

I love using Access and when I come across something I don't know how to do I usually get busy with Google but this time I haven't found anyone with the same problem.

Like a lot of people, I suspect, I can do some things in code but the complicated stuff is beyond me. I wish I had time to learn more but pressure of the job puts paid to that.

Regards.

Elaine.

 

[Trevor G]

Ok Elaine,

I have had to modify your form Fassenger_subform to show single records rather than Continuous Forms otherwise it would add all the passengers as new records, I have placed on a command button below your comments field, I think you will see it easily enough, if you go into design view on this form and then select the command button and look at opening the properties you will see events, look at the on click event and you can see the code from there.

I think with your knowledge you will recognise the field names from the table and using the field names on the form. I have also added a message box to confirm the record has been added.

I have attached a copy of the database for you.

Good luck.

 

[Snufflz]

Oh, that's brilliant, thank you.

Now I just have to add thirty or so fields from the all_records table. Will I need to put any new information into that code?

Set rst = db.OpenRecordset("T:all_records")

rst.AddNew
rst("Client name").Value = Me.Passenger_name.Value

rst("Client address").Value = Me.Passenger_address.Value

rst("Client home tel").Value = Me.Passenger_home_tel.Value
rst("Client Mobile").Value = Me.Passenger_mobile.Value
rst("Date of Birth").Value = Me.Passenger_DOB.Value


ie. the 'accident date' field on the original record also needs to be added to the new record.

Sorry to be such a pain.

Regards.

 

[Trevor G]

You would need to add the rst field and then point to the main form and then the field, so it would something like this:

rst(Accident date).value = me.form name.accident date field.value

Once you have one of them working you will need to do the same for the others.

I am out for a few hours so wont be able to pick this up again today, if you can't get it to work for you then send me an email

trevorglover@wuit.co.uk

I will then sort tomorrow. Please list the fields by name if you can't do it yourself, so I know what to add to the code.

Your not being a pain by the way, I am happy to help when I have the time.