What's the alternative to lookup columns in a combo box?

[Guest]

I'm a novice who had to quickly build a large database with forms to
demonstrate some ideas. The database works well, but now I need to neaten it
up.

I put some lookups on the table. I knew it wasn't the thing to do, but it
got me to where I wanted to be. I want to remove the lookups, but I don't
want to replace them with combo boxes on the form, because users aren't able
to directly modify the field.

Table: GtTextTable
Field: PublicationStatus
Value   Text
0     In Progress
1     Ready to Publish
2     Ready to Update
3     Publish
etc.

I want to display the numerical values as text on a form.

Form: GtTextForm
Control: PublicationStatus.

What's the aesthetic? Put the values in a table? Query?

Suggestion to Microsoft: It seems to me that (in a Combo Box's properties)
it should be possible to remove the ability to click (somebody will now tell
me this is already so).

Thank you

Peter

 

[Douglas J. Steele]

Yes, you can disable combo boxes (or any other control, for that matter).

However, there's no need for combo boxes in this case. Create a query that
joins your main table to the GtTextTable, and use that query as the
recordsource for the form. You can then display the Text in a text box.

 

[Guest]

Probably I'm too late coming back to this, but I'm trying to balance learning
something myself with completing the job.

Obviously I didn't explain very well: GtTextTable is my main table (now
called TextTable).

I experimented with various options that didn't work. Here's a reexplanation
and a failed attempt.

I want to display text in an (unbound) text box (PublicationStatus on the
form TextForm).

The text is in PublicationStatusTable which has 2 fields.

PublicationStatusID   PublicationStatusText
0           In Progress
1           Ready to Publish
2           Published
3           Ready to Update
4           Temporarily Removed
5           Permanently Removed

I tried to display the text by comparing PublicationStatusID this table with
PublicationStatusID in TextTable:

=DLookUp("[PublicationStatusText]","PublicationStatusTable","[PublicationStatusID]=Tables!TextTable![PublicationStatusID]")

But no go. Where am I going wrong?

Thank you

Peter

 

[Douglas J. Steele]

Try putting the reference to the form field outside of the quotes:

=DLookUp("[PublicationStatusText]","PublicationStatusTable","[PublicationStatusID]="
& Tables!TextTable![PublicationStatusID])

That's assuming PublicationStatusID is numeric. If it's text, you need to
include quotes:

=DLookUp("[PublicationStatusText]","PublicationStatusTable","[PublicationStatusID]="
& Chr$(34) & Tables!TextTable![PublicationStatusID] & Chr$(34))

or

=DLookUp("[PublicationStatusText]","PublicationStatusTable","[PublicationStatusID]='"
& Tables!TextTable![PublicationStatusID] & "'")

 

[Guest]

This produces: #Name? and I can't work out why. If you can't see an answer,
I'll put it aside to later.

Thanks for taking the trouble

Peter

:

Try putting the reference to the form field outside of the quotes:


=DLookUp("[PublicationStatusText]","PublicationStatusTable","[PublicationStatusID]="
& Tables!TextTable![PublicationStatusID])

That's assuming PublicationStatusID is numeric. If it's text, you need to
include quotes:


=DLookUp("[PublicationStatusText]","PublicationStatusTable","[PublicationStatusID]="
& Chr$(34) & Tables!TextTable![PublicationStatusID] & Chr$(34))

or


=DLookUp("[PublicationStatusText]","PublicationStatusTable","[PublicationStatusID]='"
& Tables!TextTable![PublicationStatusID] & "'")



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Probably I'm too late coming back to this, but I'm trying to balance
learning
something myself with completing the job.

Obviously I didn't explain very well: GtTextTable is my main table (now
called TextTable).

I experimented with various options that didn't work. Here's a reexplanation
and a failed attempt.

I want to display text in an (unbound) text box (PublicationStatus on the
form TextForm).

The text is in PublicationStatusTable which has 2 fields.

PublicationStatusID PublicationStatusText
0 In Progress
1 Ready to Publish
2 Published
3 Ready to Update
4 Temporarily Removed
5 Permanently Removed

I tried to display the text by comparing PublicationStatusID this table
with
PublicationStatusID in TextTable:


=DLookUp("[PublicationStatusText]","PublicationStatusTable","[PublicationStatusID]=Tables!TextTable![PublicationStatusID]")

But no go. Where am I going wrong?

Thank you

Peter

 

[Douglas J Steele]

The problem's likely mine: I didn't read your original question that
closely, and didn't pay attention to the fact that you were trying to
compare the PublicationStatusID in PublicationStatusTable to
Tables!TextTable![PublicationStatusID]. You can't refer to values in tables
like that.

Assuming your form is bound to TextTable (or, far better in my opinion, to a
query based on that table), try replacing
Tables!TextTable![PublicationStatusID] with Me![PublicationStatusID]. Or
have a text box that's bound to that field (it doesn't have to be visible),
and refer to that form control.

 

[Guest]

Your final suggestion was best for me

i.e., "A text box that's bound to that field (it doesn't have to be
visible), and refer to that form control."

I hadn't thought of that. It will be useful in the future.

I'm in Central America with few resources. A friend is purchasing a book for
me (probably Access Bible 2003) and bringing it next month and so then I'll
be able to sort some of these things out for myself.

In the meantime, you're a life saver.

Thank you

Peter