Texbox retrieve data depends on combo box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

yanz - 14 May 2007 07:41 GMT

Hi,Im beginner of MS Access.I've dificulties in creating a form.
I have tables which are:
tblRoom(IDRoom,NoRoom,IDBlock,IDType,IDPrice,Occupied,Notes)
tblBlock(IDBlock,NameBlock)
tblType(IDType,Type)
tblPrice(IDPrice,Price)

The form contain 1st combo that list all the NameBlock from tblBlock and
2nd combo that list all the NoRoom from tblRoom depends on 1st combo

My question is how can I make one textbox in the same form show the Type
from tblType that depend on 2nd combo and the texbox can't be editable.

Thanks for spent times reading my Question.

Reply to this Message

Ofer Cohen - 14 May 2007 11:22 GMT

Add the data you want to display to the Combo RowSource

Select Field1,Field2 From TableName

If it's includes more then one table, then add the second table and add the
nes field.
Make sure that the Column Count property in the combo includes the number of
columns.
If you want to hide this column you can use the ColumnWidth property.
2 cm;0 cm

In the text box you can refer to this column in the combo by writing in the
ControlSource
=[ComboName].Column(1)

*****
Note: the column number start with 0, so the second column will be 1
*****

By writing that in the ControlSource of the text box, the user wont be able
to edit it.

===========================
The second option will be, to use DlookUp in the ControlSource of the Text Box

= DlookUp("[FieldName]","[TableNAme]","[FieldNameForSerch]=" & [ComboName])

If the field you are searching by is string then use
= DlookUp("[FieldName]","[TableNAme]","[FieldNameForSerch]='" & [ComboName]
& "'")

Signature

Good Luck
BS"D

> Hi,Im beginner of MS Access.I've dificulties in creating a form.
> I have tables which are:
[quoted text clipped - 10 lines]
>
> Thanks for spent times reading my Question.

Reply to this Message

yanz - 15 May 2007 02:04 GMT

Hi again..I tried to make dlookup but it doesn't work..it just become #Name?.

This is my 1st combo's Row Source:
SELECT tblBlock.IDBlock, tblBlock.Nama_Block FROM tblBlock;

my 2nd combo's Row Source:
SELECT tblBilik.IDBilik, tblBilik.NoBilik, tblBilik.IDBlock,
tblBilik.IDJenis, tblBilik.IDHarga FROM tblBilik WHERE
(((tblBilik.IDBlock)=Forms![Pendaftaran Masuk]!cboNamaBlok)) ORDER BY
tblBilik.NoBilik;

Then I tried to make the textbox Control Source:
Dlookup ("[Jenis]","[tblJenis]","[IDJenis]='"& [cboNoBilik] & "'")

my Dlookup is wrong?I confused because it need the data from 2 tables..

> Add the data you want to display to the Combo RowSource
>
[quoted text clipped - 41 lines]
> >
> > Thanks for spent times reading my Question.

Reply to this Message

Ofer Cohen - 15 May 2007 07:50 GMT

Hi,

Do you have equal sign before the DlookUp, without it you'll get NAME

=Dlookup ("[Jenis]","[tblJenis]","[IDJenis]='"& [cboNoBilik] & "'")

Signature

Good Luck
BS"D

> Hi again..I tried to make dlookup but it doesn't work..it just become #Name?.
>
[quoted text clipped - 57 lines]
> > >
> > > Thanks for spent times reading my Question.

Reply to this Message

yanz - 15 May 2007 09:25 GMT

Thanks it work but contain no data. I think it should be my mistake when
called the data. I still blur how to relate those 2 tables with dlookup. I
wanna make Jenis from tblJenis appear which is IDJenis same with IDJenis from
tblBilik but tblBilik. Can you check my DLookup, have I forgot something? Or
if there is any other suggestion, it is my pleasure to hear. I'll try to
learn it. Thanks again.

> Hi,
>
[quoted text clipped - 63 lines]
> > > >
> > > > Thanks for spent times reading my Question.

Reply to this Message

yanz - 17 May 2007 09:38 GMT

Thank you a lot with your posts..it's working greatly..
I do not set the Column Count and Column Width at the combo before..
what I should do if I wanna make those textboxs can be edit? I can't use
DLookUp right?
However,thanks again..I've learn a thing today :D

> Hi,
>
[quoted text clipped - 63 lines]
> > > >
> > > > Thanks for spent times reading my Question.

Reply to this Message

Peter Hibbs - 14 May 2007 12:10 GMT

You might also want to look at :-

http://www.fontstuff.com/access/acctut10.htm

which may give you some more ideas.

Peter Hibbs.

>Hi,Im beginner of MS Access.I've dificulties in creating a form.
>I have tables which are:
[quoted text clipped - 10 lines]
>
>Thanks for spent times reading my Question.

Reply to this Message