Query based on two tables.

[daffelito]

Hi I am trying to get an value to an unbound textbox in a form, but something in my query just returns the "query statement" it selfe in the textbox.

it looks like this:

Me.Pumptyp.Value = "SELECT tblPumpsorter.Pumpnamn" & _
                   "FROM tblPumpsorter" & _
                   "WHERE tblPumpsorter.id =" & _
                   "(SELECT tblPumpgropar.Pumpsort FROM tblPumpgropar" & _
                   "WHERE tblPumpgropar.Pumpgrop = '" & Me.Pumpgrop.Value & "');"

So the value i get in the textbox is: "SELECT tblPump.... etc"

 

[JBinQLD]

G'day fella,

A good effort but things don't work that way! The easiest solution based on your example is this as the control source of your control:

=dlookup("Pumpnamn", "tblPumpsorter", id =" & dlookup("Pumpsort", "tblPumpgropar", "Pumpgrop = '" & Me!Pumpgrop & "'"))

or, for tidyness and to help you see what's going on:

dim txtPumpsort as string

txtPumpsort=dlookup("Pumpsort", "tblPumpgropar", "Pumpgrop = '" & Me!Pumpgrop & "'")

Me!pumptyp=dlookup("Pumpnamn", "tblPumpsorter", id =" & txtPumpsort

Have a great weekend, good luck!

JB

 

[PHV]

Create a query named, say, qryPumptyp:

SELECT S.Pumpnamn, G.Pumpgrop
FROM tblPumpsorter AS S INNER JOIN tblPumpgropar AS G ON S.id = G.Pumpsort

Then, in your form:

Me!Pumptyp.Value = DLookUp("Pumpnamn", "qryPumptyp", "Pumpgrop='" & Me!Pumpgrop.Value & "'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[daffelito]

Ahh, thank you mate!

That worked like a charm.
I´m quite a newbee to this so it's kind of hard to know when to use SQL or when to use VBA.

 

[JBinQLD]

no drama, happy to help. As you're new to this, I'll just mention there are other domain aggregate functions you may find use for that work in the same way as DLookup: DSum, DCount spring immediately to mind, check the help file for more...

And if you wish to execute an action query (rather than a select as you were attempting previously) look at docmd.runSQL or db.execute methods. And docmd.setwarnings false turns of the potentially scary-to-user warning about deleting, appending, updating records.

Here endeth the 1 minute crash course of "SQL thingies in VBA"


JB

 

[daffelito]

ok, thanks