Related tables in a form

[Alex]

Hi hope someone can help (if i can explain it clearly)

I am creating a form using two tables - tblTitleList + TblContact. These
are linked tables to my database. The designer is very much into using
related tables wherever possible.

In tblTitleList I have two field
Title Id = an autonumber
Title Name = text and is in this instance Mr, Ms, Mrs etc

In tblContact among other fields, I have
Title = Number
Initials = text
Surname = text

I am pulling the data from a query which links two table field Title Id and
Title and which includes field Title Name

When my user comes along to enter a record, they are touch typing and will
want to enter eg Mr J Bloggs
I need to allow the user to type in "Mr", but I need to store in field
Title, the Title Id number from tblTitleList relevant to "Mr"
I would rather not have a dropdown on the Title field since this slows the
users down.

The other issue is that is a user is viewing the record they need to see
"Mr" and not the relevant Title Id number.

Can someone tell me the best way to achieve this please?

Thanks

Alex

 

[Dave Cousineau]

Well, all you have to do is check if Mid(str, 2) = "Mr"
and if so, pull it out

the problem with this approach is that you will be using
the actual value, rather than the ID of the value, but
since there are limited values i guess thats ok

what i would do if i were you, is put the check in
LostFocus. In LostFocus, check if Mr., Ms., Mrs., Mr, Ms,
or Mrs is the start of the string, if so remove it and
update a combo-box that is not on the TabStop route

for example:

Private Sun NameBox_LostFocus()

Dim s as string
s = Left(NameBox.Value, 3)

Select Case s

Case "Mr."
TitleBox.Value = "Mr."

Case "Mr "
TitleBox.Value = "Mr."

Case "Ms."
TitleBox.Value = "Ms."

Case "Ms "
Titlebox.Value = "Ms."

Case "Mrs"
TitleBox.Value = "Mrs."

End Select

'here you would want to adjust NameBox.Value so that it
'removes the Title as well as any spaces in between

End Sub

 

[Marshall Barton]

I am creating a form using two tables - tblTitleList + TblContact. These
are linked tables to my database. The designer is very much into using
related tables wherever possible.

In tblTitleList I have two field
Title Id = an autonumber
Title Name = text and is in this instance Mr, Ms, Mrs etc

In tblContact among other fields, I have
Title = Number
Initials = text
Surname = text

I am pulling the data from a query which links two table field Title Id and
Title and which includes field Title Name

When my user comes along to enter a record, they are touch typing and will
want to enter eg Mr J Bloggs
I need to allow the user to type in "Mr", but I need to store in field
Title, the Title Id number from tblTitleList relevant to "Mr"
I would rather not have a dropdown on the Title field since this slows the
users down.

The other issue is that is a user is viewing the record they need to see
"Mr" and not the relevant Title Id number.

I would go ahead and use the combo box. It shouldn't slow
the touvh typing users down if they just type into it as if
it were a text box, but it will provide an easy check
(NotInList) if the users make up new titles or mistype an
existing one.

Most users also like the the auto complete feature so all
they have to do is hit Tab when they've entered a sufficient
number of characters to uniquely identify the desired entry
(although, in this case it's probably insignificant).

 

[APH]

Thanks guys

Alex