Append query using friendly fields

[Bcr]

Hi,
Ill try and keep this brief.
In essence, I need to give my user the ability to create a new record in a table. However, instead of presenting the user with unfriendly key field IDs I want to give them the friendly version to select.
So there is a table calles Properties. These properties have things done to them, service events, e.g. fix a tap etc.
So the ServiceEvents table stores the property the work is being done at - Property Ref, the date the work is being done - Service Date, whos is doing the work - ServicerID, and what is being serviced - ServiceItemID.
Because all these IDs are just numbers, I need to give the user a friendly way of selecting the data, e.g. instead of Property Ref which is a number, they need to see the house number and address 1, and select that which then takes the associated Property Ref No and pokes that in the table.
Same philosophy for the Servicer and the item being serviced.

The above is the design of my append query. But it wont show the fields that have blanks in the Append to row.

Can someone guide me please? Am I doing this the wrong way?

Thanks in advance,

Bcr

 

[mike60smart]

Hi,
Ill try and keep this brief.
In essence, I need to give my user the ability to create a new record in a table. However, instead of presenting the user with unfriendly key field IDs I want to give them the friendly version to select.
So there is a table calles Properties. These properties have things done to them, service events, e.g. fix a tap etc.
So the ServiceEvents table stores the property the work is being done at - Property Ref, the date the work is being done - Service Date, whos is doing the work - ServicerID, and what is being serviced - ServiceItemID.
Because all these IDs are just numbers, I need to give the user a friendly way of selecting the data, e.g. instead of Property Ref which is a number, they need to see the house number and address 1, and select that which then takes the associated Property Ref No and pokes that in the table.
Same philosophy for the Servicer and the item being serviced.

View attachment 123400


View attachment 123401


The above is the design of my append query. But it wont show the fields that have blanks in the Append to row.

Can someone guide me please? Am I doing this the wrong way?

Thanks in advance,

Bcr

Hi Bcr
Can you upload a copy of your database with no Confidential data?

 

[plog]

Am I doing this the wrong way?

Yes. No append query needed,this can all be done via forms. You should build a form with combo boxes that show the user text but store the id.

You would have a main form that displays 1 property. It's information would be at the top of the form and at the bottom would be a continuous subform that shows all the ServiceEvents of that property and would allow the user to add new ones.

That subform would have inputs for every field in ServiceEvents (except ServiceEventID). ServicerID would be a dropdown, it would show Supplier_Name from Servicers, but it would store ServicerId from Servicers in ServiceEvents. The same would occur for ServiceItems.

Here's a link to a tutorial:

Add a combo box to a view in an Access app - Microsoft Support

Use a combo box in a view so users of your app can select a value from a dropdown list instead of typing it in.

support.microsoft.com

 

[mike60smart]

Hi,
Ill try and keep this brief.
In essence, I need to give my user the ability to create a new record in a table. However, instead of presenting the user with unfriendly key field IDs I want to give them the friendly version to select.
So there is a table calles Properties. These properties have things done to them, service events, e.g. fix a tap etc.
So the ServiceEvents table stores the property the work is being done at - Property Ref, the date the work is being done - Service Date, whos is doing the work - ServicerID, and what is being serviced - ServiceItemID.
Because all these IDs are just numbers, I need to give the user a friendly way of selecting the data, e.g. instead of Property Ref which is a number, they need to see the house number and address 1, and select that which then takes the associated Property Ref No and pokes that in the table.
Same philosophy for the Servicer and the item being serviced.

View attachment 123400


View attachment 123401


The above is the design of my append query. But it wont show the fields that have blanks in the Append to row.

Can someone guide me please? Am I doing this the wrong way?

Thanks in advance,

Bcr

Hi
Do you currently have a Main Form which allows you to select a Servicer and then a SubForm to enter Details about the ServiceEvents?

Edit***
My mistake I should have said Main Form based on Properties.

 

[MajP]

If not familiar here is a good video

 

[MajP]

One thing that may be challenging for your design is what you want to do when selecting a Service Item. Like all the other combo you want to show a descriptive item name and store the temID. But you have a category which suggests that list could be very long. So there are several things you can do with the UI.
Assume I have this.

If you Just present a list of Service Items sorted by name it may be hard to find what you are looking for. So in my combo I will present a concatenated choice along with the hidden ID column.
That is the simplest solution and works well until the list gets really big. Now you can scroll to your category and then find the selection. (you are still saving the ID)

If the lists gets long you can do cascading combos where you select the category and then the items in that category. This requires some code on the form.

Service Item Category is an unbound combobox and used only for the filtering of service item combo. This works well when your list gets long.

However this cascading idea is really hard to do in a continuous form view, since the category is unbound. In that case I will build a small pop up form to do my selection instead of a combobox or if it suffices use the first concatenated design.