Dlookup based on multiple Comboboxes criteria.

[bubai]

In my business I have yearly itemized import limit by USD which I record in Quota table. In Cons table I record yearly import details. In the Balance column of _Qtb4 query I keep track of remaining amount of allowance and decide how much to import for that year. Import data input is done through Cons_input form where I have placed a text box named quotabalance. In that text box I need to have the balance amount from that query using Dlookup. But in the criteria I need to put values from 3 comboboxes - FiscalYear, Station and Catagory which should match 3 columns with identical names from _Qtb4 query. So that whenever i enter new record and fill these 3 comboboxes the text box named quotabalance should show balance amount for that particular item.


I can't figure out the appropriate way to combine them in the criteria parameter of Dlookup function. I have attached the sample database.Dlookup Prob.zip

[moke123]

I get an error that it cant find "Cons"
You didnt include that table.

[bubai]

I get an error that it cant find "Cons"
You didnt include that table.

No. Cons table is there. Just above the Quota table.

[moke123]

I've got A2010 and it wont let me open the table.

Someone else may be able to help you.

Good luck.

[Micron]

I can open it but wow. In the expression you have no spaces in front of your "ands" and there are spaces where there shouldn't be, but perhaps what's worse is that your field and control names are the same. I never allow that. So the expression criteria is basically using the same reference for the query field and the criteria (e.g. FiscalYear). Access cannot differentiate between the two. Since you cannot use Me in an expression for a form control property (on the property sheet) you'll have to write a function and update the textbox when some other event occurs (combo AfterUpdate?) - perhaps 2 places (form Current) because the control is unbound, which as I mention, just makes your life more difficult.

Looks overly complicated. I have to wonder why so many unbound fields and code rather than queries and tables that should be able to work better. I took a look at the relationships as well - usually lookup tables don't need to be part of the relationships. There are also foreign keys that are not part of any relationship.
There are naming problems
- why start with _?? It requires every such name to be enclosed in brackets
- special characters in names (#, etc)

[bubai]

- why start with _?? It requires every such name to be enclosed in brackets
- special characters in names (#, etc)

Hi there!
Actually, I used _ in front of table names to distinguish them from the regular tables, as they are lookup tables. Also they are used in front of queries which are not my main queries but used as a helper query to come to the final query. Only special character I used was ">" and "<" to denote the row source type of a field's combobox. They were named that way initially when I was much inexperienced in designing the database. Later I corrected myself as much as I could. However Access in its own right changed those characters to some other ways and I am not interfering to correct them in chase it gets corrupted.

So the expression criteria is basically using the same reference for the query field and the criteria (e.g. FiscalYear). Access cannot differentiate between the two.

This is a problem. In that case I can not use a name for 2nd time in the whole database. In top of that sometimes when I try to reference a combobox by using me keyword, it shows me Recordset objects as well as form objects (ie. combobox). And what's more confusing is that, if I select recordset object by mistake it works fine. ie it takes vales from that combobox.

I took a look at the relationships as well - usually lookup tables don't need to be part of the relationships. There are also foreign keys that are not part of any relationship.

Some tables and relationships were deleted in order to avoid ambiguity that's why the unrelated FKs.

I have to wonder why so many unbound fields and code rather than queries and tables that should be able to work better.

Unbound comboboxes werer used to populate the ImportID textbox and for some cascading comboboxes.

Since you cannot use Me in an expression for a form control property (on the property sheet) you'll have to write a function and update the textbox when some other event occurs (combo AfterUpdate?) - perhaps 2 places (form Current).

Can you tell me more on what function I better use?

I have changed the combobox names and also adjusted the blank spaces in dlookup function with And and other places and now it seems to be working. Also placed a requery to the third comboboxe's Afterupdate event in order to bypass the on currenct event of the form.

[moke123]

Actually, I used _ in front of table names to distinguish them from the regular tables, as they are lookup tables. Also they are used in front of queries which are not my main queries but used as a helper query to come to the final query. Only special character I used was ">" and "<" to denote the row source type of a field's combobox. They were named that way initially when I was much inexperienced in designing the database. Later I corrected myself as much as I could. However Access in its own right changed those characters to some other ways and I am not interfering to correct them in chase it gets corrupted.

I still cant open your cons table but I can poke around and see some other objects.
You should really correct your naming conventions as I would think what your using will eventually cause problems.
Notably you are using an octothorpe (#) in your field names. An octothorpe is used as a delimiter for dates. A dash(-) or minus sign is used in calculations.
see this link for commonly used names . . . http://access.mvps.org/access/general/gen0012.htm

It also appears that your storing multiple pieces of data in a single field.

SELECT Left([Cons]![Import_ID],7) AS [Fiscal Year], Mid([Cons]![Import_ID],9,3) AS Station, Mid([Cons]![Import_ID],13,IIf(Mid([Cons]![Import_ID],13,1)="S",6,3)) AS Category,

SELECT [_QtB1]![Fiscal Year] & [_QtB1]![Station] & [_QtB1]![Category] AS [Consignment ID], [_QtB1].SumOfBankValue FROM _QtB1;

You probably should have separate fields for FiscalYear, Station, and Category. Should there be a need to string them together you just concatenate them.
It appears you have multiple queries just to parse these fields out.

[Micron]

Only special character I used was ">" and "<"

I see that this has already been refuted. A lot of your issues now and in the future could be eliminated by using a proper naming convention. Perhaps read up and adopt or modify one that you like. Try
Naming conventions
http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names - http://allenbrowne.com/AppIssueBadWord.html

I don't see any multi value fields in the supplied tables but table Cons is designed like a spreadsheet with repeating value fields. So that and some other general stuff leads me to say that you should take a look at these subjects as well:

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

[ssanfu]

Also, there are spaces in field names.

The PK/FK fields are of type Byte - that limits the maximum number of records to 256.

The VBA Module(s) is/are missing the line "Option Explicit" as the 2nd line of the module.



I would suggest the design issues be resolved before continuing.

[bubai]

I still cant open your cons table but I can poke around and see some other objects.
You should really correct your naming conventions as I would think what your using will eventually cause problems.
Notably you are using an octothorpe (#) in your field names. An octothorpe is used as a delimiter for dates.

The database was designed on my office pc where the os is Windows 10. But in my home pc its Windows 7. Both have Access 2016. But now from my home pc I myself can't open them. I will be able to convert it to .mdb on Sunday when my office will start again.
I never used octothorpe in any name but as I said earlier it might be the result of Access converting my special character "<,>" into them.
I didn't used separate fields for FiscalYear, Station and Category in order to keep the file size to minimum. But I used PK for every field. Import ID is the field where I concatenated those 3 values from unbound combobox along with a SL combobox to give the field a unique identifier which will be easily visible to naked eyes. If I need to search by those 3 fields, I use Like * ---* search at the Import ID field. You'll know when you see the DB after I upload it.

[bubai]

Also, there are spaces in field names.

The PK/FK fields are of type Byte - that limits the maximum number of records to 256.

The VBA Module(s) is/are missing the line "Option Explicit" as the 2nd line of the module.

Hi.
Thanks for pointing out. But I deliberately made some PK fields to byte where 256 no of records will be sufficient to me in order to keep file size to minimum.
I will put "Option Explicit" to my codes then.

[bubai]

I don't see any multi value fields in the supplied tables but table Cons is designed like a spreadsheet with repeating value fields.

Dear Micron,
Many thanks that you took the time to check my db and supplied me with the links.
But can you mention in detail about the Cons table? Because I really can't find out if I have put a repeating value field there.

[moke123]

I never used octothorpe in any name but as I said earlier it might be the result of Access converting my special character "<,>" into them.

You use octothorpes in these 2 fields


What exactly is your special character? and its purpose?

I didn't used separate fields for FiscalYear, Station and Category in order to keep the file size to minimum.

You should. Your overcomplicating this.

give the field a unique identifier which will be easily visible to naked eyes.

Users shouldn't necessarily even see the Pk's. As I stated you can always concatenate the 3 fields for display if needed.

[Micron]

Because I really can't find out if I have put a repeating value field there.

I took a closer look and will have to retract that statement. It was date fields that made me think that, but they seem to apply to other things. It still appears to be designed as a spreadsheet though because you're mixing attributes of something (e.g. date for invoice, date for bond, etc.) when you should be showing only the invoice (etc) ID. I also wonder what a Cons is. Does it have a shipped attribute, or does 'shipped' apply to one of those other fields? If it does, shipped doesn't belong there either. Last but not least, checkboxes are usually a poor means of telling you anything compared to a date field, for instance. No date = not shipped. A date not only = shipped, it tells you when. If you're going to tell us that you have that date elsewhere, then you are repeating data.

You might be overly concerned about db size, but I don't understand the comment about being limited to 256 records because a field has a byte data type. Every record in that field could hold 256 bytes of data. I'm stumped as to how the field size limits the number of records in a table. Perhaps I've misunderstood what was said.

[ssanfu]

but I don't understand the comment about being limited to 256 records because a field has a byte data type.

@Micron,
I think that is in reference to my post (Post #9).

These tables
_BrandLookup,
_CategoryDescriptionLookup,
_cnfLookup,
_supplierLookup,
_UnitLookup,
& Quota
have a PK field data type of Number - Byte. The largest number a Byte field can hold is 256 (0 to 255) ..... so 256 unique records.

Table Cons has a PK field of Number - Integer. Since an integer type field has a range of -32,768 to 32,767, there could be 65535 unique records.
It doesn't make sense to me to save 3 bytes and limit the max number of records in the table to 256 - regardless if you will not have more than 256 records.


I ALWAYS have the PK field as an Autonumber type field..... or maybe I'm way off base.......