URGENT - adding a table to my query keeps the form from taking inp

[Guest]

I hae an issue that's got me pulling my hair out. I have a form, based on a
one-table query (it's being used to filter for specific projects in the
table). I added a second table to that query (no join, as one is a lookup
table of sorts, listing scores and text translations of those scores) and the
form stopped allowing me to do data entry!

I can delete the second table from the query, and just as suddenly it all
works again - but the 'lookup' that says what the score is on the project
means doesn't work, because the control source is no longer in the list!

I have tried to work around this by changing the control source of the text
box I'm using to display the data (it now reads =tables![info tbl support
level conversion].[PPQA Support Level] which doesn't give me a result.

I need it to take the field [ed calculated level] from query [qry projects]
(which is the source of this form) and display the corresponding value from
[info tbl support level conversion] where queries![qry projects].[ed
calculated level] = tables![info tbl support level conversion].[ed calculated
level] . Any help would be GREATLY appreciated!

 

[John Vinson]

I hae an issue that's got me pulling my hair out. I have a form, based on a
one-table query (it's being used to filter for specific projects in the
table). I added a second table to that query (no join, as one is a lookup
table of sorts, listing scores and text translations of those scores) and the
form stopped allowing me to do data entry!

Exactly. No Join, no updatability.

I'd suggest using a Combo Box bound to the test score and displaying
the text translation, rather than a Cartesian query.

I can delete the second table from the query, and just as suddenly it all
works again - but the 'lookup' that says what the score is on the project
means doesn't work, because the control source is no longer in the list!

I need it to take the field [ed calculated level] from query [qry projects]
(which is the source of this form) and display the corresponding value from
[info tbl support level conversion] where queries![qry projects].[ed
calculated level] = tables![info tbl support level conversion].[ed calculated
level] . Any help would be GREATLY appreciated!

If a combo box won't do the job for you, use the DLookUp function:

=DLookUp("[Ed Calculated Level]", "[info tbl support level
conversion]", <some criteria which I can't make out from your
description>)

Do you have two fields both named Ed Calculated Level?
John W. Vinson[MVP]

 

[Guest]

John,

Yes, I do have two fields named ED Calculated level. One is in the project
table (and is specific to a project) and one in the table that gives the
possible values and their text translations. They were named the same for
simplicity (hah!).

I've never used DLookup before - can I use it as th control source on a text
box?

I hae an issue that's got me pulling my hair out. I have a form, based on a
one-table query (it's being used to filter for specific projects in the
table). I added a second table to that query (no join, as one is a lookup
table of sorts, listing scores and text translations of those scores) and the
form stopped allowing me to do data entry!

Exactly. No Join, no updatability.

I'd suggest using a Combo Box bound to the test score and displaying
the text translation, rather than a Cartesian query.

I can delete the second table from the query, and just as suddenly it all
works again - but the 'lookup' that says what the score is on the project
means doesn't work, because the control source is no longer in the list!

I need it to take the field [ed calculated level] from query [qry projects]
(which is the source of this form) and display the corresponding value from
[info tbl support level conversion] where queries![qry projects].[ed
calculated level] = tables![info tbl support level conversion].[ed calculated
level] . Any help would be GREATLY appreciated!

If a combo box won't do the job for you, use the DLookUp function:

=DLookUp("[Ed Calculated Level]", "[info tbl support level
conversion]", <some criteria which I can't make out from your
description>)

Do you have two fields both named Ed Calculated Level?
John W. Vinson[MVP]

 

[John Vinson]

John,

Yes, I do have two fields named ED Calculated level. One is in the project
table (and is specific to a project) and one in the table that gives the
possible values and their text translations. They were named the same for
simplicity (hah!).

Then you'll want to be specific: use [Tablename].[ED Calculated Level]
(with your own table name of course) to disambiguate.

I've never used DLookup before - can I use it as th control source on a text
box?

Yep. Just precede it by an = sign.

John W. Vinson[MVP]

 

[Guest]

John,

I've built this statement for my text box, and it's not liking it. Where did
I put the wrong punctuation? <G>

=DLookUp("[PPQA Support Level]","[info tbl support level conversion]","[tbl
projects].[ed calculated level]"=" [info tbl support level conversion].[ed
calculated level]")

Thanks so much! I have a presenation at 10 CDT I really need to have this
working at.

John,

Yes, I do have two fields named ED Calculated level. One is in the project
table (and is specific to a project) and one in the table that gives the
possible values and their text translations. They were named the same for
simplicity (hah!).

Then you'll want to be specific: use [Tablename].[ED Calculated Level]
(with your own table name of course) to disambiguate.

I've never used DLookup before - can I use it as th control source on a text
box?

Yep. Just precede it by an = sign.

John W. Vinson[MVP]

 

[John Vinson]

I've built this statement for my text box, and it's not liking it. Where did
I put the wrong punctuation? <G>

=DLookUp("[PPQA Support Level]",
"[info tbl support level conversion]",
"[tbl projects].[ed calculated level]=" & [info tbl support level
conversion].[ed calculated level])

should be correct.

The third argument is the tricky one. It needs to be a string, a valid
SQL WHERE clause without the word WHERE; you build it up from pieces,
using the & concatenation operator to splice bits together. In this
case the two bits you need to splice are the text constant


"[tbl projects].[ed calculated level]="

and the variable (which returns a number, I hope and presume)

[info tbl support level conversion].[ed calculated level]

If the latter contains (say) 4, the resulting expression would be

[tbl projects].[ed calculated level]=4

which will retrieve that record from the level conversion table.

Thanks so much! I have a presenation at 10 CDT I really need to have this
working at.

Sorry... I'm on MDT, and I was just putting up my breakfast oatmeal at
10 your time.

John W. Vinson[MVP]

 

[Douglas J. Steele]

Sorry to butt in, John, but I didn't think you could qualify fields like
that in DLookUps.

[info tbl support level conversion] can't possibly have two fields named [ed
calculated level]. If [info tbl support level conversion] is a table, Access
wouldn't have allowed the duplicate field name to be saved. If [info tbl
support level conversion] is a query, Access will create an alias for one of
the duplicate field names along the lines of Expr1.

Afraid, though, that I'm not following exactly how [tbl projects].[ed
calculated level] enters into things.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I've built this statement for my text box, and it's not liking it. Where
did
I put the wrong punctuation? <G>

=DLookUp("[PPQA Support Level]",
"[info tbl support level conversion]",
"[tbl projects].[ed calculated level]=" & [info tbl support level
conversion].[ed calculated level])

should be correct.

The third argument is the tricky one. It needs to be a string, a valid
SQL WHERE clause without the word WHERE; you build it up from pieces,
using the & concatenation operator to splice bits together. In this
case the two bits you need to splice are the text constant


"[tbl projects].[ed calculated level]="

and the variable (which returns a number, I hope and presume)

[info tbl support level conversion].[ed calculated level]

If the latter contains (say) 4, the resulting expression would be

[tbl projects].[ed calculated level]=4

which will retrieve that record from the level conversion table.

Thanks so much! I have a presenation at 10 CDT I really need to have this
working at.

Sorry... I'm on MDT, and I was just putting up my breakfast oatmeal at
10 your time.

John W. Vinson[MVP]

 

[Guest]

Thanks to you both, guys. After some fiddling with it, I came up with the
following, which works.

=DLookUp("[info tbl support level conversion].[PPQA Support Level]","[info
tbl support level conversion]","Forms![frm projects].[ed calculated
level]=[info tbl support level conversion].[ed calculated level]")

Sorry to butt in, John, but I didn't think you could qualify fields like
that in DLookUps.

[info tbl support level conversion] can't possibly have two fields named [ed
calculated level]. If [info tbl support level conversion] is a table, Access
wouldn't have allowed the duplicate field name to be saved. If [info tbl
support level conversion] is a query, Access will create an alias for one of
the duplicate field names along the lines of Expr1.

Afraid, though, that I'm not following exactly how [tbl projects].[ed
calculated level] enters into things.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I've built this statement for my text box, and it's not liking it. Where
did
I put the wrong punctuation? <G>

=DLookUp("[PPQA Support Level]",
"[info tbl support level conversion]",
"[tbl projects].[ed calculated level]=" & [info tbl support level
conversion].[ed calculated level])

should be correct.

The third argument is the tricky one. It needs to be a string, a valid
SQL WHERE clause without the word WHERE; you build it up from pieces,
using the & concatenation operator to splice bits together. In this
case the two bits you need to splice are the text constant


"[tbl projects].[ed calculated level]="

and the variable (which returns a number, I hope and presume)

[info tbl support level conversion].[ed calculated level]

If the latter contains (say) 4, the resulting expression would be

[tbl projects].[ed calculated level]=4

which will retrieve that record from the level conversion table.

Thanks so much! I have a presenation at 10 CDT I really need to have this
working at.

Sorry... I'm on MDT, and I was just putting up my breakfast oatmeal at
10 your time.

John W. Vinson[MVP]