Add records to a table - but filtering records shown using query

[Heathey94]

Not really sure this is the right section for this but here goes:



Is it possible to use a query to pull through certain records in a table (or multiple tables), but then change values and add them as new records to the table (as opposed to the normal method of just editing the current record)?

[JoeM]

You can use an Append Query in order to create/add new records from a query to a table.

There are lots of tutorials out there (some visual on YouTube) that you can find with a Google Search, if you would like to see examples of how to set them up.

[Heathey94]

Excellent, thank you. Searching for how to do something is kind of hard unless you know what it is called...

[Heathey94]

I have one other question, is it possible to use a tick box to enter a number onto a table?

i.e. there are some questions but they only apply to some versions of a form. If the tick box is selected, it enters the form version (shown elsewhere on the form) into the table

If that's not clear, just say and I'll try to rephrase it.

[JoeM]

Excellent, thank you. Searching for how to do something is kind of hard unless you know what it is called...

You are welcome!

I have one other question, is it possible to use a tick box to enter a number onto a table?

i.e. there are some questions but they only apply to some versions of a form. If the tick box is selected, it enters the form version (shown elsewhere on the form) into the table

If that's not clear, just say and I'll try to rephrase it.

Not quite clear on that. However, since this is a brand new question, you probably would want to post it to its own new thread, so it shows up as a new unanswered question (and others will see it too).

[Heathey94]

OK will do, thanks.

I've just realised you can't base a form on an append query (unless I'm going about it wrong) so would you suggest creating two queries, one an append query and the other a select query to only pull through the fields required?

[JoeM]

I would probably use a multi-query approach.

What I often do is have a selection form that has all the information that I need/want, then use that form to dynamically build a query to run.

Without knowing the exact details and structure of your database, and understanding exactly the workflow, it is hard to be specific as to what might be the best approach.

[Heathey94]

OK, yeah I think I get it. how would I get them to run? My initial thought (from my attempts so far) is that if I use the select query to pull in the data, then change text in the fields so they can be added once I run the append query, the text on the original fields will also be changed, so I would need to run the append query, undo my changes to the form and then close the form... correct? Or maybe I've just got the completely wrong end of the stick.

[JoeM]

How many records/fields are we talking about here?

If it simply a matter of importing a single record, making some editing, and then committing that to the table as a new record, I think I might take an entirely different approach.
- I might start out with an unbound Form that has all the field represented as text boxes.
- Then, I might have a button which populates those fields with the values from a specific record I designate.
- Then I can make edits to any fields that I want
- Then I would have another button which would use VBA to build and run an Append Query to write the record back to my table as new record.

That approach is going to involve quite a bit of VBA.

Maybe if you can walk us through an example of what you are trying to do (and why), we would have a better understanding of why you are trying to do this, and may be able to offer other alternatives as well.

I

[Heathey94]

It is likely to be a lot of records - I have attached the database so you can see what I am working with.

As you can see from TblBehaviour, there are a lot of questions for each form, each of which will need to be added as new records.

The process will also be used for the AddLowRiskAssessment form (and Medium/High also), and is likely to add less records, but there is a chance it will not

Hopefully that's clear, if not just ask!

Thanks for all your help

[JoeM]

Looks like you have a lot going on in that database. I am afraid that in order to give you any kind of good advice, it would require a thorough understanding of your structure/design and goals. Unfortunately, I don't really have the time to do that level of consulting. I am sorry, but I don't think I have anything else to offer on this.

[Heathey94]

I think most of it is not relevant here, I'll tell you what is going on and if you still do not have the time then fair enough.

I am creating a database to track assessments made by social workers to determine if a child is at risk of being sexually exploited. I have done most of the back work, just have the two questions I've already mentioned left to tackle.

The forms which begin with an 'o' are from the old version of the database before I restructured it - just there so I don't forget anything.
There are 3 categories of risk which a question can be classed as - low, medium and high and since there are so many in each category, I decided it would be best to give each category its own form.
So, I need the form to be based on a select query which chooses the "low" risk questions (for example), and allows the user to put the response to each question for the child.

[JoeM]

I think this database is much different than I would have designed. Whenever doing data entry, I always used Bound forms, so the data is automatically written back to the underlying tables upon data entry, so there is no need to have an update button or Append Queries to write the data back to the tables. I really don't design databases to work like that, so I am probably not the best person to guide you through such a process.

[Heathey94]

I could probably use a bound form, I just thought it would be better for the user if there were update buttons as it is similar to the system in use in other areas of the company. I will still need to use append queries however, as the data will need to be inputted as new rows to the table.

I am willing to change the system if it does not require too much work as I really need to get it ready for input soon.

[orange]

Just saw your post and your data base.
I agree with JoeM that readers would need to know more about your processes and environment to offer focused responses.

Can you identify 1 form/issue to work on?
Can you describe the logic (in plain English) to SELECT the records you are interested in?