What DAO's Are Not Dynamic?

[Dal]

DAO (Database Access Object is probably the wrong term but trying my best to explain). I need my db to be as dynamic as possible. I created a dLookup in a form referencing a table called tBills. I have just changed the name of the table to tBills2 & it seems that references in the expression editor are not dynamic. I know this is the case in VBA also. What do I need to be aware of the common pitfalls in db design?

It is a text box coloured yellow. I imagine if had used the wizard it would be dynamic. But I'm trying to learn properly how it truly works. What would be the best way to enter a text box fed from another table so it is fully dynamic? I know I could use VBA but I want to keep VBA to a minimum as it seems that surely VBA is not required for this.





Also I can't see the source properly. With lots of tables... similar fields can get confusing. The record source only shows the field; how do you find out the source table? Take 2nd pic e.g. showing MainHeaderID I cannot see the table source? Disregard the fMainHeaders at the top because this could be renamed/ deleted.

[June7]

Your attachments are invalid. EDIT: And they are now fixed.

[Welshgasman]

The fact that you now have tbills2 sounds alarm bells. What happened to tbills?

[Dal]

Experimenting trying to learn.

[pbaldy]

FYI I deleted your moderated post since you'd already reposted the question and received replies. I was slow on the moderating, sorry.

[Dal]

No worries, it thought it was a repeated question because I posted a VBA solution I found.

[Micron]

A bound control's source is the field. The form recordsource is the table or query, so if you want to know that, select the form and look at the data tab. That is the only way for bound controls.

[Dal]

Thanks but how do you know what table/ query if you have many similar fields. There does not appear to be an indicator.

[Micron]

Not sure I understand your question. If I build a form and its recordsource is a table or query, I know which table it is by looking at the form or report recordsource as I stated. If you have several tables that have fields with the same name, it either means you need to adopt a better naming convention, or your tables are not sufficiently normalized. The former often results from using names like ID for the primary key field in every table. ID tells you nothing about the field or the table it's in. PO_ID or PoID or something similar tells you it's the ID field in the Purchase Order table, and that I would know just from looking at the control source property. Other field names would not help so much, such as "Length", so just look at the form record source. If none of that helps, maybe post a pic of your relationships so that we can see some field names that you have.

[June7]

If by dynamic you mean it will calculate for each record, expression needs to reference value from current record. Complete the WHERE argument for your DLookup with concatenation.

=DLookup("Bill", "tBills", "BillID=" & [BillID])

[Welshgasman]

I *think* the o/p is talking about, they had a dlookup on tbills but then had to change it to tbills2, but their dlookup and anything else did not adjust to suit?

[Gicu]

I think the OP is looking for Name Autocorrect or similar functionality, it has improved since was first introduced but many programmers still shy away from it:
https://support.microsoft.com/en-us/...4-a93c10a9d98b
http://allenbrowne.com/bug-03.html
Cheers,

[June7]

Auto-correct will not modify references in quotes. I think OP's real problem is invalid DLookup expression. See post 10.

[Dal]

Auto-correct will not modify references in quotes. I think OP's real problem is invalid DLookup expression. See post 10.

Correct, the problem is the arguments of DLookup are provided in strings. I am looking for a dynamic solution.

I *think* the o/p is talking about, they had a dlookup on tbills but then had to change it to tbills2, but their dlookup and anything else did not adjust to suit?

Correct

I think the OP is looking for Name Autocorrect or similar functionality, it has improved since was first introduced but many programmers still shy away from it:
https://support.microsoft.com/en-us/...4-a93c10a9d98b
http://allenbrowne.com/bug-03.html

You've grasped the issue completely, thank you, perfectly linked article many thanks.

To others in the future: to see the main source of the form click the top left black rectangle selecting the entire form, Properties > Data > Record Source - the main source of the entire form is listed here.

[Micron]

To others in the future: to see the main source of the form click the top left black rectangle selecting the entire form, Properties > Data > Record Source - the main source of the entire form is listed here.

As was stated in post 7.