Dlookup based on multiple Comboboxes criteria.

[bubai]

You use octothorpes in these 2 fields

Oh, my bad. Those two fields were recently added and as they were not used in any calculation or join I just forgot about them. But I will correct them.

What exactly is your special character? and its purpose?

Right now they don't have any purpose and as I said earlier, they were added when I was unaware about their perils. But its a rigorous tusk to correct them now as they have so many interconnections.

You should. Your overcomplicating this.

I will explain this when I upload the converted version of the DB. But in short I actually have a numbered PK for every field. But the unique text field (Import ID) is mostly a visual reminder to me because data insertion to a field can not be done in one go. There are couple of stages that have to be gone through to complete a field ie invoice creation, shipment, warehousing etc. For that i need to come back to the field and fill up them part by part.

As I stated you can always concatenate the 3 fields for display if needed.

Import ID is a concatenation to field no 2 to 5. If I keep separate fields for each part then there will be 3 extra columns and will add to the file size.

[Micron]

so 256 unique records.

Yes, I misunderstood what you were saying. Thought you meant you could only have 255 records - period. I agree with the rest of your last post too. As I mentioned, the concern about db or table size is misguided.
BTW, did you notice that the FK field was a lookup field? Notwithstanding that a lookup field is usually a bad thing, to have it as a foreign key makes absolutely no sense. You don't pick and choose your foreign key values - they are supposed to come from the pk field of the parent table.

[ssanfu]

BTW, did you notice that the FK field was a lookup field?

Oops.... I missed that.

Hmmm.... Well, back to the drawing board (or white board, cardboard, ......)

[moke123]

@Steve - Are you able to open the Cons table using A2010?
I get this error

I've never encountered it before and have always been able to open other versions of Access.

[ssanfu]

My computer HDD started intermittently refusing to boot (No Boot Device Available) so I removed it and tried to clone it; no go; then I took it to a shop..... they couldn't clone it either. I am trying a couple of other things, but I think I'm looking at a new install.

All of that to say I lost my 2010 Office (for now).

So this weekend I am going to try and install Win7, then downgrade to Win10. Can't install Office2010 (32 bit) on Win10 (32/64 bit) for some reason.
Worst thing is I lost Firefox with Tabs on Bottom....


I'll take the dB home and try to open the table on my laptop.....

[bubai]

Notwithstanding that a lookup field is usually a bad thing, to have it as a foreign key makes absolutely no sense. You don't pick and choose your foreign key values - they are supposed to come from the pk field of the parent table.

I might be mistaken, but my understanding was whatever may be the lookup field showing, it's original value underneath would be the ID(bound column). If it's not the case then i have to correct them as well.

[moke123]

I might be mistaken, but my understanding was whatever may be the lookup field showing, it's original value underneath would be the ID(bound column). If it's not he case then i have to correct them as well.

This made me wonder whether using a lookup field in a table consumed a table connection. I just ran a quick test and it apparently does.

[Micron]

my understanding was whatever may be the lookup field showing, it's original value underneath would be the ID(bound column)

You are basically correct in that assumption. Your PK value should become a foreign key value for sure, but I've never seen anyone design it so that you can pick it from a list. Maybe in your case it is set up so that you cannot pick the wrong value, but when you have a one to many relationship the association is usually handled with master and child fields with subforms. I don't even recall seeing a subform. I guess if you can keep it working you'll stay the course, but you might find it to be a bumpy ride, so to speak.

[bubai]

This made me wonder whether using a lookup field in a table consumed a table connection. I just ran a quick test and it apparently does.

That is a useful disclosure!
I have converted the DB to .mdb. If you would like to have a look.
ConvertedDlookup Prob.zip

[ssanfu]

@Moke123,

Try this version. It seems to open in A2010.
I used A365 and did a Save As in format 2007 - 2010

[bubai]

Maybe in your case it is set up so that you cannot pick the wrong value, but when you have a one to many relationship the association is usually handled with master and child fields with subforms.

Yes my idea was to see the names instead of IDs when creating queries and check the lookup table directly at a glance. But I think I better set it right as the database is not yet put into operation. And I do have subforms in mind.

[moke123]

Thanks for the file steve.

I cant say that I understand everything in your cons table but it seems to me your packing a lot into it.

Is your fiscal year based on some other date such as invoice date or Bond date, etc?
Rather than yes/no fields for shipped and DocArrived wouldn't a date be better?

[bubai]

I cant say that I understand everything in your cons table but it seems to me your packing a lot into it.

Is your fiscal year based on some other date such as invoice date or Bond date, etc?
Rather than yes/no fields for shipped and DocArrived wouldn't a date be better?

Each record of the Cons tables represents each unique consignment of a category to a station on a fiscal year.
Fiscal year is just a usual financial year (July-June). Invoice date Bond date etc refers to those unique invoice, bond etc that are assigned to each consignment.
Micron also advised me to use date instead of check boxes which I will keep in mind. However there are some cases where I don't need to know the dates.

Many thanks for your effort in checking my DB.

[moke123]

The reason I asked about fiscal year is it may not be necessary to store it.

For instance if the invoice date will always fall within the fiscal year you dont need to store it but can retrieve it on demand with a function

Function GetFiscalYear(Dt As Variant, Optional StartMonth As Integer = 6) As Variant
If Not IsDate(Dt) Then Exit Function
If Month(Dt) >= StartMonth Then
   GetFiscalYear = (Year(Dt) + 1)
Else
   GetFiscalYear = Year(Dt)
End If
End Function

You appear to be just storing the calendar year.

[bubai]

The reason I asked about fiscal year is it may not be necessary to store it.

For instance if the invoice date will always fall within the fiscal year you don't need to store it but can retrieve it on demand with a function

Invoice date does not necessarily match the fiscal year. Fiscal year value is needed to match with the Quota table's Fiscal year. Quota table is my approved limit of importable value. By comparing yearly import and quota I know how much more to import. Shown in _Qtb4 query.
However, can I ask you one more favor?
That is -
I have a Payment_CNF table for CNF (freight forwarders) payments. In the Cons table I have field for FK_CNF id and CNF bills. In the Payment_CNF table payments are made for each consignment's CNF bill amounts which are in the Cons table. That's why FK_ConsID field in the Payment_CNF table to identify for what consignment is the payment for. The problem is, most of the times CNF bills are determined after the delivery is completed. So, often times payments are made in advance. ie before the CNF bills are recorded in the Cons table. So, I have to keep blank (Null) in the FK_ConsID field in the Payment_CNF table when recording the advance payments. Later when I get the bill, I have to assign FK_ConsID to those payment records with Nulls. But, the payment amount usually does not equate the bill amounts. ie. suppose I pay the CNF in advance of USD 12 and for 3 consignments my bill is total 15 (5+5+5). So, I need to assign $10 to 2 consignment and $2 to one consignment (partially paid). Later the remaining $3 will be paid. I can't figure out a systematic way to handle this.
I have added Payment_CNF table to the attached DB.
Dlookup Prob1.zip