Text Box

[Paul]

Hello list,

I am creating a Model Railroad inventory database and I
have hit a brick wall.

I am trying to get the Railroad name to automatically
appear in a text box when I select its reporting marks.
Here is what I have.

I have a table called Reporting Marks. It has three
columns, RailroadId (Primary Key, auto number),
ReportMarks (Railroad Reporting Marks) and RoadName
(Railroad Name). All but the primary key field are text
fields. I also have a form to use to add more Railroads
as I need them. This table and form work just fine.

I have another form with an unbound text box named
Text48, and a combo box called Repmark. What I want to
happen is when I select a reporting mark in the combo
box, have the railroad name associated with it appear in
the text box automatically. I have an event programmed
into the After Updates event for the text box. The code
for this is:

Private Sub Text48_AfterUpdate()
Dim varRn As Variant
varRn = DLookup_
("Roadname", "Reporting_Marks", "ReportMarks")
Me![Text48] = varRn
End Sub

What I want to happen is when I select a Reporting Mark,
I want the Railroad associated with that Reporting mark
to come up in the text box. So far all I can get is the
first railroad name in the list comes up no matter what
reporting mark field I select.

Any help wouild be greatly appreciated. I have been
beating myself up with this for a couple of days now and
I can't get it to work.

Thanks in advance.

Paul

 

[Douglas J. Steele]

The third argument of the DLookup function is supposed to be a WHERE clause
(without the word WHERE)

You're not telling it WHAT ReportMarks value to lookup. Something like:

varRn = DLookup_
("Roadname", "Reporting_Marks", _
"ReportMarks = '" & combo1 & "'")

where combo1 is the combo box you've selected the ReportMarks.

However, the AfterUpdate event of the text box isn't the appropriate place
for that code. It should be in the AfterUpdate event of the combo box.

There is an easier way, though. You can add the Roadname to your combo box
(you can hide it if you want), and then you can retrieve it from the combo
box in the AfterUpdate event, rather than looking it up in the database.

If it's the second column of the combo box, you'd refer to it as
combo1.Columns(1) (the column numbering starts at 0)

 

[Ozzone]

Probably the easiest way is to modify the combo box.

1. Make sure the rowsource pulls all 3 fields.

2. Make Column Count = 3 columns. The ID, Marks and
Name. Simply hide the ID and Name column in the dropdown
by making the Column Widths like 0;1;0.

3. On the AfterUpdate event, have:

Me.Text498 = Me.Repmark.Column(2) ' 0=ID, 1=Marks, 2=Name


RailroadId (Primary Key, auto number),
ReportMarks (Railroad Reporting Marks) and RoadName
(Railroad Name).

 

[Paul]

Doug,

You Da Man!! That worked. The next round's on me.

Paul

-----Original Message-----
Probably the easiest way is to modify the combo box.

1. Make sure the rowsource pulls all 3 fields.

2. Make Column Count = 3 columns. The ID, Marks and
Name. Simply hide the ID and Name column in the dropdown
by making the Column Widths like 0;1;0.

3. On the AfterUpdate event, have:

Me.Text498 = Me.Repmark.Column(2) ' 0=ID, 1=Marks, 2=Name


RailroadId (Primary Key, auto number),
ReportMarks (Railroad Reporting Marks) and RoadName
(Railroad Name).

-----Original Message-----
Hello list,

I am creating a Model Railroad inventory database and I
have hit a brick wall.

I am trying to get the Railroad name to automatically
appear in a text box when I select its reporting marks.
Here is what I have.

I have a table called Reporting Marks. It has three
columns, RailroadId (Primary Key, auto number),
ReportMarks (Railroad Reporting Marks) and RoadName
(Railroad Name). All but the primary key field are text
fields. I also have a form to use to add more Railroads
as I need them. This table and form work just fine.

I have another form with an unbound text box named
Text48, and a combo box called Repmark. What I want to
happen is when I select a reporting mark in the combo
box, have the railroad name associated with it appear in
the text box automatically. I have an event programmed
into the After Updates event for the text box. The code
for this is:

Private Sub Text48_AfterUpdate()
Dim varRn As Variant
varRn = DLookup_
("Roadname", "Reporting_Marks", "ReportMarks")
Me![Text48] = varRn
End Sub

What I want to happen is when I select a Reporting Mark,
I want the Railroad associated with that Reporting mark
to come up in the text box. So far all I can get is the
first railroad name in the list comes up no matter what
reporting mark field I select.

Any help wouild be greatly appreciated. I have been
beating myself up with this for a couple of days now and
I can't get it to work.

Thanks in advance.

Paul

.

.

 

[John Vinson]

I have another form with an unbound text box named
Text48, and a combo box called Repmark. What I want to
happen is when I select a reporting mark in the combo
box, have the railroad name associated with it appear in
the text box automatically. I have an event programmed
into the After Updates event for the text box. The code
for this is:

Private Sub Text48_AfterUpdate()
Dim varRn As Variant
varRn = DLookup_
("Roadname", "Reporting_Marks", "ReportMarks")
Me![Text48] = varRn
End Sub

What I want to happen is when I select a Reporting Mark,
I want the Railroad associated with that Reporting mark
to come up in the text box. So far all I can get is the
first railroad name in the list comes up no matter what
reporting mark field I select.

That's because that's what you're asking for. You have no criteria in
the DLookUp expression.

I'd suggest instead setting the control source of the textbox to

=Repmark.Column(2)

to display the third column of the combo's rowsource query for the
selected record. The Roadname should emphatically NOT be stored
redundantly in this table, only in the Railroads table, so you're
right to make this textbox unbound!