Hi Im having this scenario: 3 1

[Claulle]

Hi

Im having this scenario:
3 tables: Main, Prod and Docs
Main consists of 3 entities: Repno(PK), Type, Number. Type is a 2 valued field with the values "prod" and "docs"
Number is a lookup from either of the below written tables (prod and docs)

Prod has 2: Number(PK), Description
Docs has 2: Number(PK), Description


My question is: Is it possible in [main.Number] to determine from the value in [main.Type] what table to get the values from?

Somethink like this in pseudo-code:
IF [main.type] = 'docs' THEN SELECT * FROM Docs
ELSE
IF [main.type] = 'prod' THEN SELECT * FROM Prod


Hope this explains what im looking for.

In advance thanks
Claulle

 

[DanChard]

Try creating a query with the following SQL behind it:

select main.*, prod.*
from main inner join prod on main.number = prod.number
where main.type = 'prod'
union
select main.*, docs.*
from main inner join docs on main.number = docs.number
where main.type = 'docs'
order by main.repno;

Hope this is what you were looking for...

 

[Claulle]

Hi DanChard

Its not quite what i ment. Im sorry for not expressing me right. In my table (main) i have a entity where you select either "Docs" or "Prod". Thats the only 2 values that are allowed in that field. Next to that field i want a dropdownbox where the data depends wether you select "docs" or "prod" in the previous field. I have 2 tables. 1 for docs and 1 for prod, where i want the main table to get the data from. Is that possible at all?

Thanks alot for the answer tho

 

[DanChard]

Hi again.

I've been playing with this for a while and can't work out how to make this work using lookups. For me, the problem lies within making Access see the 'type' that has just been selected ('prod' or 'docs') so that it can be used to determine the possible selections for 'number'.

Had you thought about using a Form to do this? A form can be set up so that it looks exactly the same as the standard table view - this being "DataSheet" mode. You could then add a couple of combo boxes to the form which will give you tighter control over exactly what is displayed.

If going ahead with the Form idea is OK then I give you some pointers as I've managed to get it working on my test version. Otherwise, I have no idea how you'd do it using a table and lookup columns - sorry!

 

[Claulle]

Hi

Yeah, I have been playing with the thought and come to that point as well (the form-thing). Actually i think Forms is the only way i can solve this thing. I have absolutely no problem using forms or querys in any way. So if you dont mind point wich direction i should go in this Access-wilderness

I better spend the weekend reading my old SQL book again

Thanks a ton !

PS: I have to leave computer(work) in 1 hour, and will be back on monday. (just FYI if you dont get to respond to me before i need to go, so you dont think im rude because im not responding.) Have a nice day!

 

[DanChard]

Right then, let's get this licked!

Create a new form with the following settings:

Format - Default View - Datasheet
Data - Record Source = main
(drag 'repno' field onto form from box that comes up)


create a new combo box on the form with following settings (cancel the wizard):
Data - Control Source = type
Data - Row Source Type = Value List
Data - Row Source = 'docs';'prod'
Data - Default Value = 'docs'
Other - Name = type


create a new combo box on the form with following settings (cancel the wizard):
Format - Column Count = 2
Format - Column Widths = 0cm;10cm
Data - Control Source = number
Data - Row Source Type = Table/Query
Other - Name = number
Event - On GotFocus - [...] (little button) - Code Builder - OK:

(paste the following code between the 'Private Sub ... ' and 'End Sub' lines:

If Nz(Me.type, "&quot = "" Then
Me.number.RowSource = ""
Else
Me.number.RowSource = "select number, description from " & Me.type & " order by description;"
End If

This should be all you need to know. Hopefully this will set up the form as you wanted it. Please post again if things don't work out...

Good luck!

 

[Claulle]

Thanks a bunch!

Tho i didnt get it to work proberly ( i got a box wich i could insert a value wich i after entering a number i could select it in a combo box. You have helped me over a nice breakpoint so i think i should be able to figure it out sometime next week. If not i will ask again

Thank you alot for your time and effort! (Grats on the star )


Greetings from Denmark
- Claulle