Avatar billede frederikschack Nybegynder
13. januar 2013 - 17:48 Der er 26 kommentarer og
1 løsning

Hent værdi fra andet sted i tabellen

Jeg har en tabel med følgende felter:

KategoriAutoID (nøgle)
OverordnedKategoriAutoID (reference til samme tabel)
KategoriID
SubkategoriID
Kategori

Hvor OverordnedeKategoriAutoID referer til KategoriAutoID i samme tabel.

Jeg vil gerne kunne sætte værdien i [KategoriID] til at være, den overordnede [KategoriID] og [SubkategoriID] i den pågældende tabel. Jeg forestiller mig noget i retningen af:

DLookUp("KategoriID";"Kategorier";"KategoriAutoID='" & [OverordnedeKategoriAutoID]&"'") & SubkategoriID

Men man kan tilsyneladende ikke bruge DLookUp i et beregnet felt. Jeg får fejlmeddelelsen "The expression DLookUp("Kat....cannot be used in a calculated column.?

Mit spørgsmål er så hvordan jeg ellers kan realisere det?
Avatar billede terry Ekspert
13. januar 2013 - 18:05 #1
DLookUp("KategoriID";"Kategorier";"KategoriAutoID='" & [OverordnedeKategoriAutoID]&"'") & SubkategoriID


whats the & SubkategoriID for?
Avatar billede terry Ekspert
13. januar 2013 - 18:06 #2
after the dlookup?
Avatar billede terry Ekspert
13. januar 2013 - 18:07 #3
Where are you using the dlookup?

if you are creating (entering) the record in a form then I would set the value using dlookup in teh formsa before update event for the record
Avatar billede frederikschack Nybegynder
13. januar 2013 - 18:14 #4
The table contains a hierarchical category structure, that's why I have the self reference.

I would like to use the dLookUp to get a value from the same table (thy higher category) and put it into the current table. It shouldn't be in a query.

In this way I should be able to create a sort of recursive function that creates a hierarchical ID like.

1
1.1
1.2
1.2.1
1.2.2
1.3
etc.
Avatar billede frederikschack Nybegynder
13. januar 2013 - 18:16 #5
I could also do with some VBA code, but I only used VBA for Excel so far, so I would need some help for that.
Avatar billede terry Ekspert
13. januar 2013 - 18:25 #6
You should add the parent category value when you create the record. Normally you would create the record in a form.

You cantm which I think you are trying to do, use Dlookup as a calcualted field in the table design.

Another alternative is to choose Lookup Wizard as the data type for the field. Once you have followed the wizard you will see that the field is now a drop down (combo box) so you can choose the parent vale from a list.
Avatar billede frederikschack Nybegynder
13. januar 2013 - 18:30 #7
I know how to create a drop down, but that's not the point. What I need is to somehow get a value from another entry in the same table, which will always be the higher category in the hierarchy.
Avatar billede frederikschack Nybegynder
13. januar 2013 - 18:35 #8
So the category "Animals" might have the sub category 1, and the category "Mamals" have the sub category 1 and the category "Elephants" have the sub category 2.

What I want is to dynamically create a hierarchical ID like the one below, nothing to be entered exept for the sub category "SubkategoriID":


1 Animals
1.1 Mamals
1.1.1 Whales
1.1.2 Elephants
Avatar billede terry Ekspert
13. januar 2013 - 18:39 #9
I suggest that you use a form for entering the data. Then you can use VBA to get teh value of the parent category using VBA
Avatar billede terry Ekspert
13. januar 2013 - 18:40 #10
I can help you with vba if you make a form for entering data.
Avatar billede frederikschack Nybegynder
13. januar 2013 - 18:43 #11
I have actually done something like that before and would be able to do that. But what I would like to do, not to depend on all forms to have this function, is to have the function in the table.
Avatar billede terry Ekspert
13. januar 2013 - 18:45 #12
"is to have the function in the table"

You cant:o(

If the tabel were in an SQL server then you could use a stored procedure, but it isnt, so you have tio use what is available and I suggest VBA
Avatar billede terry Ekspert
13. januar 2013 - 18:46 #13
"not to depend on all forms to have this function"

You wouldnt have to
Avatar billede terry Ekspert
13. januar 2013 - 18:46 #14
Just going for something to eat :o)
Avatar billede frederikschack Nybegynder
13. januar 2013 - 18:48 #15
If the function is in the form, then every form that I make to access that table would need to have the function. Also if I decide to recreate the form, I would need to transport the code.
Avatar billede frederikschack Nybegynder
13. januar 2013 - 18:48 #16
Of course :)
Avatar billede terry Ekspert
13. januar 2013 - 19:26 #17
Normally you would have a form for creating the records, so its only in this form you would need the function.

Once the record is create then you dont need the function to view or even change, unless of course to wanted it to be in another parent category.
Avatar billede frederikschack Nybegynder
13. januar 2013 - 19:34 #18
Exactly and then it should correct itself recursively. It's not something that has to be entered, it's something that should be created out of what was entered.
Avatar billede terry Ekspert
13. januar 2013 - 19:36 #19
"and then it should correct itself recursively"?

not sure I understand what you mean here
Avatar billede frederikschack Nybegynder
13. januar 2013 - 19:44 #20
It's a hierarchy, so if you move one of the upper levels of the hierarchy, then the levels below should be corrected accordingly.
Avatar billede terry Ekspert
13. januar 2013 - 19:52 #21
OK, we can look at that another time, first we need to create a record.

Can you send me a dB (example) with some existing data so I can see exactly what your after?

Also, if possible, a form where you create your records.

ekspertenATsanthell.dk
change AT to @

I will make some code which adds parent ID

You may not get an answer this evening as I'm going to see some TV soon.
Avatar billede frederikschack Nybegynder
13. januar 2013 - 20:02 #22
So far it's very conceptual, so there is only the table and I've sent it to you.
Avatar billede terry Ekspert
13. januar 2013 - 20:07 #23
OK,I'll take a look and get back to you ASAP
Avatar billede frederikschack Nybegynder
13. januar 2013 - 20:09 #24
Super, thanks. But don't waste too much time if you do it in forms, because I need something that can run outside the forms.
Avatar billede terry Ekspert
13. januar 2013 - 22:33 #25
can you explain in more detail "need something that can run outside the forms"?

No point inmaking something  which maybe cant be used.
Avatar billede frederikschack Nybegynder
14. januar 2013 - 06:35 #26
Well, I need something that updates the table, like a sort of a calculated field. I don't need something that makes me dependent on what happens in different forms.

So if I change the category "Animals" from 1 to 2, in the table or through any form, then what is below should be changed:

1 Animals
1.1 Mamals
1.1.1 Whales
1.1.2 Elephants

To this

2 Animals
2.1 Mamals
2.1.1 Whales
2.1.2 Elephants
Avatar billede frederikschack Nybegynder
12. november 2014 - 08:24 #27
Ok, ingen svar på dette spørgsmål.
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Dyk ned i databasernes verden på et af vores praksisnære Access-kurser

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester