Combo box problem...

[Louie Warren]

I am using Access 2000.

I have a combo box. It pulls a field from a table that
shows an acronym. The user can then select one of the
acronyms, or enter a new one. Next to the combo box, I
have a description field. If an acronym in the table is
selected, then the associated description should be
displayed and the field should be read-only. If a new
acronym is entered, a description must be entered. I
can't seem to get this to work like I want. Any ideas as
to how to accomplish this task? Thanx

L

 

[Howard Brody]

Private Sub Combo1_AfterUpdate()

Dim i as integer
Dim strAcr as String

' does entered value exist in table?
strAcr = [Combo1]
i = DCount("[Acronym]","tblTable", _
"[Acronym]=' & strAcr & "'")

' if do, display desc and lock control
If i > 0 Then
Dim strDesc as String
strDesc = DLookUp("[Description]", _
"tblTable", "[Acronym]=' & strAcr & "'")
[txtDescription] = strDesc
txtDescription.Locked = True
Else
txtDescription.Locked = False
txtDescription.SetFocus
MsgBox "Please enter the description for " _
& "the new code you just entered"
End If

End Sub

The only downside to this code is that it does not add the
new acronym and description to the table. You'll have to
do that after.

Hope this helps!

Howard Brody

 

[Louie Warren]

I'm new at vb so be gentle...
I assume...

strAcr = [Combo1]

Combo1 is the name of the combo box I am working with?

i = DCount("[Acronym]","tblTable", _

Acronym is the field name in my table?
tblTable is the table I am pulling from?
BTW... the compiler didn't like the "'" I removed the
inner one and it compiled. I get a substring out of range
error, but it compiles.

strDesc = DLookUp("[Description]", _

Description is the field name in my table?
txtDescription is the display box on the form?

I'll learn this eventually. Thanx!