Combo Box problem

[Bruce Rodtnick]

I have a combo box with a row source from a table (cboTapeName). The
combo box has a row source from a table,
TapesNames, with two columns, TapeID (autonumber) and TapeName (text)
I've set the column count to 2 and the column
widths to 0";5". The bound column is 1.

I'm using DLookup to populate four other combo & text boxes:

Private Sub cboTapeName_AfterUpdate()
Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "TapeID = " & Me!cboTapeName

' Look up product's unit price and assign it to UnitPrice control.
Me!cboTapeName = DLookup("TapeName", "TapeNames", strFilter)
Me!cboProducer = DLookup("Producer", "TapeNames", strFilter)
Me!cboVideographer = DLookup("Videographer", "TapeNames", strFilter)
Me!DateShot = DLookup("DateShot", "TapeNames", strFilter)
Me!Notes = DLookup("Notes", "TapeNames", strFilter)
Me!txtTapeID = DLookup("TapeID", "TapeNames", strFilter)


But when I run the form I get an error:
The value you entered isn't valid for this field.
For example, you may have entered text into a numeric field or a number
that is larger than the fieldsize setting permits.

When I change the column widths of the combo box (cboTapeName) to 1";5"
I don't have the problem, but then the ID shows
in the combo box and I don't want that.

Why is this happening?

I'm running 2000

Bruce Rodtnick

 

[John Vinson]

I have a combo box with a row source from a table (cboTapeName). The
combo box has a row source from a table,
TapesNames, with two columns, TapeID (autonumber) and TapeName (text)
I've set the column count to 2 and the column
widths to 0";5". The bound column is 1.
ok...

I'm using DLookup to populate four other combo & text boxes:

Private Sub cboTapeName_AfterUpdate()
Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "TapeID = " & Me!cboTapeName

' Look up product's unit price and assign it to UnitPrice control.
Me!cboTapeName = DLookup("TapeName", "TapeNames", strFilter)
Me!cboProducer = DLookup("Producer", "TapeNames", strFilter)
Me!cboVideographer = DLookup("Videographer", "TapeNames", strFilter)
Me!DateShot = DLookup("DateShot", "TapeNames", strFilter)
Me!Notes = DLookup("Notes", "TapeNames", strFilter)
Me!txtTapeID = DLookup("TapeID", "TapeNames", strFilter)

DLookUp is probably the LEAST desirable way to do this! If these four
fields exist in the TapeNames table, consider basing the Combo on a
query selecting all the fields. Use TapeID as the Control Source; for
the Row Source use

SELECT TapeID, TapeName, Producer, Videographer, DateShot, Notes
FROM TapeNames ORDER BY TapeName;

Set the Column Widths property to 0;5;0;0;0;0 so that only the
tapename is visible.

Then put four TEXTBOXES - not combos, there's NO point in having a
combo box if it only has one value - on the form with control sources

=cboTapeName.Column(2) to cboTapeName.Column(5)

to pull the producer and so on from the combo box itself.

If Notes is a Memo field you can't include it in the combo; instead
set the control source of Notes to

=dLookUp("Notes", "TapeNames", "[TapeID] = " &
Forms!yourformname!cboTapeID)

 

[Bruce Rodtnick]

GREAT! Works like a champ, once I got all the kinks wrung out. That is SO
much easier and it saved me a whole table that I didn't need.

Thanx for your patience.

B

I have a combo box with a row source from a table (cboTapeName). The
combo box has a row source from a table,
TapesNames, with two columns, TapeID (autonumber) and TapeName (text)
I've set the column count to 2 and the column
widths to 0";5". The bound column is 1.
ok...

I'm using DLookup to populate four other combo & text boxes:

Private Sub cboTapeName_AfterUpdate()
Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "TapeID = " & Me!cboTapeName

' Look up product's unit price and assign it to UnitPrice control.
Me!cboTapeName = DLookup("TapeName", "TapeNames", strFilter)
Me!cboProducer = DLookup("Producer", "TapeNames", strFilter)
Me!cboVideographer = DLookup("Videographer", "TapeNames", strFilter)
Me!DateShot = DLookup("DateShot", "TapeNames", strFilter)
Me!Notes = DLookup("Notes", "TapeNames", strFilter)
Me!txtTapeID = DLookup("TapeID", "TapeNames", strFilter)

DLookUp is probably the LEAST desirable way to do this! If these four
fields exist in the TapeNames table, consider basing the Combo on a
query selecting all the fields. Use TapeID as the Control Source; for
the Row Source use

SELECT TapeID, TapeName, Producer, Videographer, DateShot, Notes
FROM TapeNames ORDER BY TapeName;

Set the Column Widths property to 0;5;0;0;0;0 so that only the
tapename is visible.

Then put four TEXTBOXES - not combos, there's NO point in having a
combo box if it only has one value - on the form with control sources

=cboTapeName.Column(2) to cboTapeName.Column(5)

to pull the producer and so on from the combo box itself.

If Notes is a Memo field you can't include it in the combo; instead
set the control source of Notes to

=dLookUp("Notes", "TapeNames", "[TapeID] = " &
Forms!yourformname!cboTapeID)

 

[Bruce Rodtnick]

Oh, one more thing. How would you suggest that I handle the problem if the
tape I enter is not on the list? I have been having the code open a pop-up
that will allow me to enter new information into the table, but before the
pop-up ,opens I'm getting an error message that I have entered wrong data
type. And when I exit the pop-up, I can't get the form to requery
cboTapeName so I can use the new tape.

B

I have a combo box with a row source from a table (cboTapeName). The
combo box has a row source from a table,
TapesNames, with two columns, TapeID (autonumber) and TapeName (text)
I've set the column count to 2 and the column
widths to 0";5". The bound column is 1.
ok...

I'm using DLookup to populate four other combo & text boxes:

Private Sub cboTapeName_AfterUpdate()
Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "TapeID = " & Me!cboTapeName

' Look up product's unit price and assign it to UnitPrice control.
Me!cboTapeName = DLookup("TapeName", "TapeNames", strFilter)
Me!cboProducer = DLookup("Producer", "TapeNames", strFilter)
Me!cboVideographer = DLookup("Videographer", "TapeNames", strFilter)
Me!DateShot = DLookup("DateShot", "TapeNames", strFilter)
Me!Notes = DLookup("Notes", "TapeNames", strFilter)
Me!txtTapeID = DLookup("TapeID", "TapeNames", strFilter)

DLookUp is probably the LEAST desirable way to do this! If these four
fields exist in the TapeNames table, consider basing the Combo on a
query selecting all the fields. Use TapeID as the Control Source; for
the Row Source use

SELECT TapeID, TapeName, Producer, Videographer, DateShot, Notes
FROM TapeNames ORDER BY TapeName;

Set the Column Widths property to 0;5;0;0;0;0 so that only the
tapename is visible.

Then put four TEXTBOXES - not combos, there's NO point in having a
combo box if it only has one value - on the form with control sources

=cboTapeName.Column(2) to cboTapeName.Column(5)

to pull the producer and so on from the combo box itself.

If Notes is a Memo field you can't include it in the combo; instead
set the control source of Notes to

=dLookUp("Notes", "TapeNames", "[TapeID] = " &
Forms!yourformname!cboTapeID)

 

[John Vinson]

Oh, one more thing. How would you suggest that I handle the problem if the
tape I enter is not on the list? I have been having the code open a pop-up
that will allow me to enter new information into the table, but before the
pop-up ,opens I'm getting an error message that I have entered wrong data
type. And when I exit the pop-up, I can't get the form to requery
cboTapeName so I can use the new tape.

Set the combo box's Limit to List property to TRUE, and then put code
in the combo's Not In List event to enter the new data. The Not In
List code will do the requery and suppress the error messages if you
set it up correctly. See http://www.mvps.org/access/forms/frm0015.htm
for sample code.