forms - lookup & fill in fields from table

[Chrissy]

Hi, I am a new user. I have 2 tables - CompanyDetails and StudentDetails. I
am designing a form (at first with just CompanyDetails table as the source).
The primary key (no duplicates) is CompanyName, a text field. When the user
enters the first field on the form I want the user to be able to select an
existing CompanyName and have the other 6 fields from the table displayed in
the other fields in the form OR enter a new CompanyName and other fields. How
do I do this in a simple way? Do I use a combo box - but how do I get the
other fields to fill in automatically from the table? I have no programming
skills.

Many thanks in advance

 

[Rick Brandt]

Hi, I am a new user. I have 2 tables - CompanyDetails and StudentDetails. I
am designing a form (at first with just CompanyDetails table as the source).
The primary key (no duplicates) is CompanyName, a text field. When the user
enters the first field on the form I want the user to be able to select an
existing CompanyName and have the other 6 fields from the table displayed in
the other fields in the form OR enter a new CompanyName and other fields. How
do I do this in a simple way? Do I use a combo box - but how do I get the
other fields to fill in automatically from the table? I have no programming
skills.

One of us is confused

You have an existing table of company data in which you want a company to be
entered only once (makes sense do far). Now you have bound a form to that table
and you want people to be able to enter existing companies in that form? That's
where you lose me.

Sounds like you want this control that the user types in to be a search control,
one that navigates in the form's Recordset to the *existing* record that matches
what they typed and if no match exists then you want them taken to the new
record position so they can make a new entry. If that is the case then this
control that they type in must be a separate unbound control with some code
associated with its AfterUpdate event. You cannot use the same control that
displays the company name in existing records to ALSO be the control used for
navigation/filtering.

If I'm not understanding what you want please clarify. If you DO want a
navigation type of control then one of the choices offered by the ComboBox
wizard will do this for you automatically. Just drop a new one on the form and
choose the option to "Find a record based on my selection".

 

[Chrissy]

Sorry for the confusion. The purpose is to make data entry easier - ie not
enter data that already exists (given that the field is set to no
dulplicates). If the user enters an existing CompanyName I want it to
fillin/display the rest of the fields with the data from the table. If the
CompanyName that the user enters does not exist I want the user to enter all
fields to create a new record in the table. Does that make sense?
Thanks

 

[Rick Brandt]

Sorry for the confusion. The purpose is to make data entry easier - ie not
enter data that already exists (given that the field is set to no
dulplicates). If the user enters an existing CompanyName I want it to
fillin/display the rest of the fields with the data from the table. If the
CompanyName that the user enters does not exist I want the user to enter all
fields to create a new record in the table. Does that make sense?
Thanks

As I stated before though the question is WHERE would the user enter this
company name?

If you open a bound form by default you are positioned at the first record. So
a TextBox bound to the company name field will already have a company name in
it. If the user just starts typing a different value into that control then
they are changing that first record (not what you want).

If you open the form to a new blank record and they start typing a company name
that already exists then it does no good to fill in the rest of the fields with
data from the existing record because they will still be creating a new record.
Of course when they attempt to save it they will get a duplicate key error and
will be forced to cancel the record anyway.

What might be close to what you want is to open the form to the new record
position (setting Data Entry property of the form to yes is the easiest way to
do this) and after they enter the company name you can test to see if they have
entered a name that already exists using code in the AfterUpdate event of the
TextBox. When it is a duplicate all you need to do is display a message and
cancel the record. The code could be something like...

If DCount("*", "TableName", "CompanyName = '" & Me!CompanyName & "'") > 0 Then
MsgBox "Company already exists"
Me.Undo
End If

 

[Chrissy]

Thanks. I probably haven't fully explained - I have 2 tables - CompanyDetails
and StudentDetails. CompanyDetails has a 1 to many relationship to
StudentDetails. The primary key in CompanyDetails is CompanyName. This field
also exists in the StudentDetails record (but not the primary key).
Eventually (when I get the first bit going) I want to have a main form -
based on the CompanyDetails table - and a subform based on the StudentDetails
table. When the user enters the CompanyName in the main form, if it exists I
want to display all the fields for that record in the main form, either make
no changes or edit some fields, then go to the Subform to enter
StudentDetails (so I don't want to cancel the record). If the CompanyName
doesn't exist I want the user to enter the new CompanyDetails then go to the
Subform to enter StudentDetails.

The user would enter the CompanyName in the first field on the form (which I
agree should be a new blank record, thanks for that). I don't know if this
field should be a a separate unbound control rather than the actual field in
the table???. I was thinking that maybe the user should select a CompanyName
from a combo box (prevents entering a slightly different version) and display
all the fileds in the main form in case the user needs to alter some fields
(such as contact name or phone number) OR if the CompanyName doesn't exist
allow the user to enter all details for a new record. THEN go to the Subform
to enter StudentDetails.

OR am I completely on the wrong track and should have 2 separate forms - one
to enter/edit CompanyDetails and another to enter/edit StudentDetails?

Thank you so much for taking the time to help.

 

[John Vinson]

Thanks. I probably haven't fully explained - I have 2 tables - CompanyDetails
and StudentDetails. CompanyDetails has a 1 to many relationship to
StudentDetails. The primary key in CompanyDetails is CompanyName. This field
also exists in the StudentDetails record (but not the primary key).
Eventually (when I get the first bit going) I want to have a main form -
based on the CompanyDetails table - and a subform based on the StudentDetails
table. When the user enters the CompanyName in the main form, if it exists I
want to display all the fields for that record in the main form, either make
no changes or edit some fields, then go to the Subform to enter
StudentDetails (so I don't want to cancel the record). If the CompanyName
doesn't exist I want the user to enter the new CompanyDetails then go to the
Subform to enter StudentDetails.

What it sounds like you actually want to do is to use a Combo Box to
*FIND* (not fill in!!!) an existing Company record. The combo box
wizard will help you do that. If it doesn't find a record, it will go
to the new record and let you enter a new company.

The user would enter the CompanyName in the first field on the form (which I
agree should be a new blank record, thanks for that). I don't know if this
field should be a a separate unbound control rather than the actual field in
the table???. I was thinking that maybe the user should select a CompanyName
from a combo box (prevents entering a slightly different version) and display
all the fileds in the main form in case the user needs to alter some fields
(such as contact name or phone number) OR if the CompanyName doesn't exist
allow the user to enter all details for a new record. THEN go to the Subform
to enter StudentDetails.

Exactly. Make the CompanyName - or, better, the CompanyID, since names
aren't generally good primary keys - the Master/Child Link Field.

John W. Vinson[MVP]

 

[Chrissy]

Thanks so much. I'll give that a try.

 

[Chrissy]

I have a combo box working now but...when I select an item from the combo box
it moves to the correct record number from the table but only displays the
data from one field on the form. How do I get it to display all the fields on
the form? The fields on the form are exactly the same as the fields in the
table.

In the combo wizard I selected "I want the combo box to look up the values
in a table/query" and "Remember the value for later use". Thanks.

 

[Chrissy]

Oops - I was wrong about it moving to the correct record. The record number
displayed is 1 as I have set Data Entry to yes on the Forms Properties. So
obviously it is not retrieving the record. If I continue to enter data I get
an error message at the end because I am trying to save a duplicate record. I
want to actually retrieve the record and display all fileds after selecting
from the combo box - then either edit it or make no change. Is there a way of
achieving this? Have I set up the combo box wrong or do I need to put some
sort of expression in After Update?

The other problem I am having is that when I enter a new record in the form
(no selection from the combo box) and move to the next blank record the
combo box is not updated with the previous entry. The combo box is however
updated after I exit from the form and go back into it.
Thanks.

 

[John Vinson]

Oops - I was wrong about it moving to the correct record. The record number
displayed is 1 as I have set Data Entry to yes on the Forms Properties.

The Data Entry property of a Form very specifically PROHIBITS the form
from viewing existing records - only the blank new record is
available. Turn it off.

So obviously it is not retrieving the record. If I continue to enter data I get
an error message at the end because I am trying to save a duplicate record. I
want to actually retrieve the record and display all fileds after selecting
from the combo box - then either edit it or make no change. Is there a way of
achieving this? Have I set up the combo box wrong or do I need to put some
sort of expression in After Update?

You must use an UNBOUND combo for this purpose, and yes, you must have
some VBA code in the AfterUpdate [Event Procedure]. The combo box
wizard will write the code for you if you select the option "Use this
combo to find a record".

What is the Control Source of the combo? What is its Row Source? Do
you have anything in the event properties?

If you could post the names of the relevant fields, I could suggest
some code for you if the wizard doesn't work for you.

The other problem I am having is that when I enter a new record in the form
(no selection from the combo box) and move to the next blank record the
combo box is not updated with the previous entry. The combo box is however
updated after I exit from the form and go back into it.

Sounds like you need to requery the combo, probably in the AfterUpdate
event of the Form.

John W. Vinson[MVP]

 

[Chrissy]

Thanks John for your patience and help. I now have all existing records
displaying OK when I select from the combo box. (I changed the Data Entry
property on the form to NO - thanks). However I now can't add a new record.
If I don't make a selection from the combo box and type something in which
doesn't exist and then try to enter new details for a new record I am
overwriting an existing record (The record number is 1 when I open the form).
The combo box properties are:-
Control Source - empty (the combo box field in design view shows Unbound)
Row source type - Table/Query
Row Source - CustomerCompanyDetails (the name of the table)
Bound Column - 1
Limit to list - No
There is nothing in the Event properties.

I need to be able to add new records and also update the combo box list
after adding a new record.
The field names are:-
CompanyName - primary key and the field that the combo box searches on and
displays (I know you said to make it a company ID but then I ended up with
more problems because a user will search for the name)
StreetAddress
Suburb
Postcode
ContactName
ContactphoneNumber

Many, many thanks
--
Chrissy

Oops - I was wrong about it moving to the correct record. The record number
displayed is 1 as I have set Data Entry to yes on the Forms Properties.

The Data Entry property of a Form very specifically PROHIBITS the form
from viewing existing records - only the blank new record is
available. Turn it off.

So obviously it is not retrieving the record. If I continue to enter data I get
an error message at the end because I am trying to save a duplicate record. I
want to actually retrieve the record and display all fileds after selecting
from the combo box - then either edit it or make no change. Is there a way of
achieving this? Have I set up the combo box wrong or do I need to put some
sort of expression in After Update?

You must use an UNBOUND combo for this purpose, and yes, you must have
some VBA code in the AfterUpdate [Event Procedure]. The combo box
wizard will write the code for you if you select the option "Use this
combo to find a record".

What is the Control Source of the combo? What is its Row Source? Do
you have anything in the event properties?

If you could post the names of the relevant fields, I could suggest
some code for you if the wizard doesn't work for you.

The other problem I am having is that when I enter a new record in the form
(no selection from the combo box) and move to the next blank record the
combo box is not updated with the previous entry. The combo box is however
updated after I exit from the form and go back into it.

Sounds like you need to requery the combo, probably in the AfterUpdate
event of the Form.

John W. Vinson[MVP]