Which field to use in form

[Gaztry80]

Hi again,

I try to understand what would be the best way to update a status field in a table by my created form.
Situation is the following: tblRequest has a StatusId, which is linked to tblStatus.
I have created a form with and have placed the StatusId with standard value "1" (corresponding to Open). So when i new record is entered it has status 1 > Open.
However, I want also to be able to change the status of the Request by a dropdown instead of changing the StatusId directly. Also when a new record is entered, I want to have a dropdown with the different Status possiblities and choose one of those, instead of chosing the StatusId.
What would be the most appropriate way to establish this? Please find my demodb in the attachment.

*Please note >all tables in my real database are "linked tables" to a sql server.

Already many thanks for learning!

 

[mike60smart]

Hi again,

I try to understand what would be the best way to update a status field in a table by my created form.
Situation is the following: tblRequest has a StatusId, which is linked to tblStatus.
I have created a form with and have placed the StatusId with standard value "1" (corresponding to Open). So when i new record is entered it has status 1 > Open.
However, I want also to be able to change the status of the Request by a dropdown instead of changing the StatusId directly. Also when a new record is entered, I want to have a dropdown with the different Status possiblities and choose one of those, instead of chosing the StatusId.
What would be the most appropriate way to establish this? Please find my demodb in the attachment.

*Please note >all tables in my real database are "linked tables" to a sql server.

Already many thanks for learning!

Hi
See the attached

 

[Gaztry80]

Hi
See the attached

Hi
Thanks for the quick reply. In your DemoDB each time a new record is put in with for example Status "Open", it will create a new StatusId

 

[arnelgp]

also this one, check the StatatusID in design view.

 

[mike60smart]

Hi
Thanks for the quick reply. In your DemoDB each time a new record is put in with for example Status "Open", it will create a new StatusId

The example I sent you shows why you should not include the Status table in your query which the form is based on

The form in this case should just be based on the tblRequests.

See this modified example.

 

[Gaztry80]

Thanks for the reply both ! This helps a lot. Just for my understanding:
If they are not linked by the query, how does access know to take the StatusId and not the Status?
Because 2 columns are in the select statement (1.)StatusId, 2.)Status) and then with setting "Columnwide > 0cm,2cm"
Is this because their is a relationship made on the ClientId?
However, when I delete this relationship, access still recognize that the fields belong to each other.
Is this just the capability of the program or do I overlook something in the settings?

Writing down this question and review, i think it is because of the "Dependency Column" right?

 

[mike60smart]

Thanks for the reply both ! This helps a lot. Just for my understanding:
If they are not linked by the query, how does access know to take the StatusId and not the Status?
Because 2 columns are in the select statement (1.)StatusId, 2.)Status) and then with setting "Columnwide > 0cm,2cm"
Is this because their is a relationship made on the ClientId?
However, when I delete this relationship, access still recognize that the fields belong to each other.
Is this just the capability of the program or do I overlook something in the settings?

Writing down this question and review, i think it is because of the "Dependency Column" right?

In the Form Design View when you create a Combobox using the Wizard to select an Item from a List from
another table, you are asked what field you will use to Store the Value Selected.
In this case we are going to use the StatusID.
So Access stores the Primary KEY from tblStatus as the Foreign Key in tblRequest

 

[Gaztry80]

In the Form Design View when you create a Combobox using the Wizard to select an Item from a List from
another table, you are asked what field you will use to Store the Value Selected.
In this case we are going to use the StatusID.
So Access stores the Primary KEY from tblStatus as the Foreign Key in tblRequest

Is it logical that loading speed / performance is dropping when adding this combobox to the form.
This form is a subform in another overview and when I add the combobox the performance is significantly dropping...

 

[arnelgp]

Writing down this question and review, i think it is because of the "Dependency Column" right?

view the StatusID combobox in design view.
you will see the Bound Column = 1, which means that the First Column
in the Row Source will be saved to the table, which is tblStatus.StatusId.

 

[Gaztry80]

Please see attached database, where i have recreated my db.
I have converted all linked tables to local tables.
However, in my real database Form1 acts unpredicatable fast and slow after I add the combobox.
I have applied a filter on customer, but don't know if this is the most efficient way to do.
Do you think their is a smarter/more efficient way to show the form / use the data?

 

[mike60smart]

Please see attached database, where i have recreated my db.
I have converted all linked tables to local tables.
However, in my real database Form1 acts unpredicatable fast and slow after I add the combobox.
I have applied a filter on customer, but don't know if this is the most efficient way to do.
Do you think their is a smarter/more efficient way to show the form / use the data?

The standard layout for this is a Main Form based on the Clients table with a Subform based on the Requests

See the modified attached Db

 

[Gaztry80]

The standard layout for this is a Main Form based on the Clients table with a Subform based on the Requests

See the modified attached Db

Thanks!
When I take your solution the initial load of the form takes sometimes 30 seconds. When the form has opened, all data population works instant. This is with all the linked tables. Is this just the downside of using linked tables? Looks like their is no smarter way. By the way, I have also created in sql server the primary and foreign keys.

 

[mike60smart]

Thanks!
When I take your solution the initial load of the form takes sometimes 30 seconds. When the form has opened, all data population works instant. This is with all the linked tables. Is this just the downside of using linked tables? Looks like their is no smarter way. By the way, I have also created in sql server the primary and foreign keys.

The Form loads instantly on my system

 

[Gaztry80]

The Form loads instantly on my system

I think it is because I have put in the tblClient into the query. Now it works instant, when I delete this tbl.
Thanks for the lessons, I really have to keep in mind to think like a data waterfall haha

 

[mike60smart]

Glad you found a solution

 

[Gaztry80]

Glad you found a solution

Well I was to fast. The form load and works instant when I make from tblClient a local table.
Unfortunately, when I leave tblClient as a linked table it takes the form 20-30 seconds to load.
When it is loaded it works stable. It is strange, since I only do the following: "right mouse > make local table", which will fix the problem.
*tblClient has just 7 records and 2 columns.

 

[mike60smart]

Well I was to fast. The form load and works instant when I make from tblClient a local table.
Unfortunately, when I leave tblClient as a linked table it takes the form 20-30 seconds to load.
When it is loaded it works stable. It is strange, since I only do the following: "right mouse > make local table", which will fix the problem.
*tblClient has just 7 records and 2 columns.

Can you upload your current version of the database?

 

[Gaztry80]

Can you upload your current version of the database?

Please see attached file. When opening form "frmClient" it takes about 20-30 seconds with linked tables.
In the uploaded database I have made the tables local.

 

[mike60smart]

See the attached

 

[Gaztry80]

See the attached

Hi Mike, I forgot to delete the "ClientProdID", which is used to link the data to another subform.
Which you guys teached me in my first post: https://www.fmsinc.com/MicrosoftAcc...ink Child,based on a control on the main form