Data entry property & lookup

M

Maarkr

I would like to have the users open up a form in Add mode and populate some
of the fields from a parameter query. My intent is to use a parameter query
to match their last name and last 4 of their ssn to fill in some basic
personal info on the form, then they fill out the rest. I don't want them to
have access to all records like you get when you just open a form in edit
mode. Maybe you have some other ideas on how to do that? I haven't tried
coding a lookup or recordset to fill it in yet...just hoping to save time by
asking for suggestions.
 
K

Klatuu

The better way to do this would be to put an unbound combo box on your form.
Make its record source a query that returns the fields you want to populate.
Use the combo's After Update event to populate the fields.

Now, I do question your database design. If you already have the person's
name and SSN, you should not be duplicating it in another table. What you
should do is have a field in this table that would store the foreign key to
the table when the person's info already is.
 
M

Maarkr

Excellent point on the design...in my case, the table is already filled with
some personal info (name, phone, address) for a thousand people and I want
each person to privately open up their partial record and fill in the rest
of the field data. I 'm working on several dbs at once and get my questions
mixed. I had put an unbound combo box in but didn't set the query up
correctly...doh!...so in one case, I didn't need to populate anything, just
have the unbdcbo open up the pqry.

BUT, in my other case, I want to open the form in data entry or Add mode and
run this code that works fine in edit mode. It looks at the last record to
see who the InitRecr is and displays the next InitRecr in the Text208
unbdtxtbox.

DoCmd.GoToRecord acForm, "frmReferral", acLast
If [InitRecr] = "John" Then
DoCmd.GoToRecord , , acNewRec
[Text208] = "Carol"
Else
If [InitRecr] = "Carol" Then
DoCmd.GoToRecord , , acNewRec
[Text208] = "Van"
Else
If [InitRecr] = "Van" Then
DoCmd.GoToRecord , , acNewRec
[Text208] = "John"
Else
End If
End If
End If

Sorry for the confusion and thx..
 
K

Klatuu

There are a number of problems here.
Going to the last record in a recordset will not necessarily, in fact
probably will not, take you to the last record you entered. There is no set
order of records in a table. Each new record is put whereever is convenient
at the moment. And, last record depends on whatever sort order is currently
in place. Therefore, you will need to come up with a scheme to determine the
most recently entere record. If you have an autonumber field and you have
the autonumber set to increment, the highest number in that field would be
the most recently entered or you can add a date/time stamp field and populate
in when you create a new record.

Also, you should not be hard coding peoples' names. Just a bad practive.

And last, Data Entry mode for a form only allows adding new records. You
will not even be able to see existing records.
--
Dave Hargis, Microsoft Access MVP


Maarkr said:
Excellent point on the design...in my case, the table is already filled with
some personal info (name, phone, address) for a thousand people and I want
each person to privately open up their partial record and fill in the rest
of the field data. I 'm working on several dbs at once and get my questions
mixed. I had put an unbound combo box in but didn't set the query up
correctly...doh!...so in one case, I didn't need to populate anything, just
have the unbdcbo open up the pqry.

BUT, in my other case, I want to open the form in data entry or Add mode and
run this code that works fine in edit mode. It looks at the last record to
see who the InitRecr is and displays the next InitRecr in the Text208
unbdtxtbox.

DoCmd.GoToRecord acForm, "frmReferral", acLast
If [InitRecr] = "John" Then
DoCmd.GoToRecord , , acNewRec
[Text208] = "Carol"
Else
If [InitRecr] = "Carol" Then
DoCmd.GoToRecord , , acNewRec
[Text208] = "Van"
Else
If [InitRecr] = "Van" Then
DoCmd.GoToRecord , , acNewRec
[Text208] = "John"
Else
End If
End If
End If

Sorry for the confusion and thx..

Klatuu said:
The better way to do this would be to put an unbound combo box on your form.
Make its record source a query that returns the fields you want to populate.
Use the combo's After Update event to populate the fields.

Now, I do question your database design. If you already have the person's
name and SSN, you should not be duplicating it in another table. What you
should do is have a field in this table that would store the foreign key to
the table when the person's info already is.
Click to expand...
Click to expand...
 
M

Maarkr

that answers that...I'll delete the code and try something else...thx

Klatuu said:
There are a number of problems here.
Going to the last record in a recordset will not necessarily, in fact
probably will not, take you to the last record you entered. There is no set
order of records in a table. Each new record is put whereever is convenient
at the moment. And, last record depends on whatever sort order is currently
in place. Therefore, you will need to come up with a scheme to determine the
most recently entere record. If you have an autonumber field and you have
the autonumber set to increment, the highest number in that field would be
the most recently entered or you can add a date/time stamp field and populate
in when you create a new record.

Also, you should not be hard coding peoples' names. Just a bad practive.

And last, Data Entry mode for a form only allows adding new records. You
will not even be able to see existing records.
--
Dave Hargis, Microsoft Access MVP


Maarkr said:
Excellent point on the design...in my case, the table is already filled with
some personal info (name, phone, address) for a thousand people and I want
each person to privately open up their partial record and fill in the rest
of the field data. I 'm working on several dbs at once and get my questions
mixed. I had put an unbound combo box in but didn't set the query up
correctly...doh!...so in one case, I didn't need to populate anything, just
have the unbdcbo open up the pqry.

BUT, in my other case, I want to open the form in data entry or Add mode and
run this code that works fine in edit mode. It looks at the last record to
see who the InitRecr is and displays the next InitRecr in the Text208
unbdtxtbox.

DoCmd.GoToRecord acForm, "frmReferral", acLast
If [InitRecr] = "John" Then
DoCmd.GoToRecord , , acNewRec
[Text208] = "Carol"
Else
If [InitRecr] = "Carol" Then
DoCmd.GoToRecord , , acNewRec
[Text208] = "Van"
Else
If [InitRecr] = "Van" Then
DoCmd.GoToRecord , , acNewRec
[Text208] = "John"
Else
End If
End If
End If

Sorry for the confusion and thx..

Klatuu said:
The better way to do this would be to put an unbound combo box on your form.
Make its record source a query that returns the fields you want to populate.
Use the combo's After Update event to populate the fields.

Now, I do question your database design. If you already have the person's
name and SSN, you should not be duplicating it in another table. What you
should do is have a field in this table that would store the foreign key to
the table when the person's info already is.
--
Dave Hargis, Microsoft Access MVP


:

I would like to have the users open up a form in Add mode and populate some
of the fields from a parameter query. My intent is to use a parameter query
to match their last name and last 4 of their ssn to fill in some basic
personal info on the form, then they fill out the rest. I don't want them to
have access to all records like you get when you just open a form in edit
mode. Maybe you have some other ideas on how to do that? I haven't tried
coding a lookup or recordset to fill it in yet...just hoping to save time by
asking for suggestions.
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

okay, post back if you have more questions
--
Dave Hargis, Microsoft Access MVP


Maarkr said:
that answers that...I'll delete the code and try something else...thx

Klatuu said:
There are a number of problems here.
Going to the last record in a recordset will not necessarily, in fact
probably will not, take you to the last record you entered. There is no set
order of records in a table. Each new record is put whereever is convenient
at the moment. And, last record depends on whatever sort order is currently
in place. Therefore, you will need to come up with a scheme to determine the
most recently entere record. If you have an autonumber field and you have
the autonumber set to increment, the highest number in that field would be
the most recently entered or you can add a date/time stamp field and populate
in when you create a new record.

Also, you should not be hard coding peoples' names. Just a bad practive.

And last, Data Entry mode for a form only allows adding new records. You
will not even be able to see existing records.
--
Dave Hargis, Microsoft Access MVP


Maarkr said:
Excellent point on the design...in my case, the table is already filled with
some personal info (name, phone, address) for a thousand people and I want
each person to privately open up their partial record and fill in the rest
of the field data. I 'm working on several dbs at once and get my questions
mixed. I had put an unbound combo box in but didn't set the query up
correctly...doh!...so in one case, I didn't need to populate anything, just
have the unbdcbo open up the pqry.

BUT, in my other case, I want to open the form in data entry or Add mode and
run this code that works fine in edit mode. It looks at the last record to
see who the InitRecr is and displays the next InitRecr in the Text208
unbdtxtbox.

DoCmd.GoToRecord acForm, "frmReferral", acLast
If [InitRecr] = "John" Then
DoCmd.GoToRecord , , acNewRec
[Text208] = "Carol"
Else
If [InitRecr] = "Carol" Then
DoCmd.GoToRecord , , acNewRec
[Text208] = "Van"
Else
If [InitRecr] = "Van" Then
DoCmd.GoToRecord , , acNewRec
[Text208] = "John"
Else
End If
End If
End If

Sorry for the confusion and thx..

:

The better way to do this would be to put an unbound combo box on your form.
Make its record source a query that returns the fields you want to populate.
Use the combo's After Update event to populate the fields.

Now, I do question your database design. If you already have the person's
name and SSN, you should not be duplicating it in another table. What you
should do is have a field in this table that would store the foreign key to
the table when the person's info already is.
--
Dave Hargis, Microsoft Access MVP


:

I would like to have the users open up a form in Add mode and populate some
of the fields from a parameter query. My intent is to use a parameter query
to match their last name and last 4 of their ssn to fill in some basic
personal info on the form, then they fill out the rest. I don't want them to
have access to all records like you get when you just open a form in edit
mode. Maybe you have some other ideas on how to do that? I haven't tried
coding a lookup or recordset to fill it in yet...just hoping to save time by
asking for suggestions.
Click to expand...
Click to expand...
Click to expand...
 
M

Maarkr

FYI, I ended up adding a field to the lookup table, and put the persons' name
who is next in line beside another name, then running a query to look at the
last record, who entered it, and returning the new 'next in line' field. If
they add or change people, they can edit the table. Works great without any
code.

Klatuu said:
okay, post back if you have more questions
--
Dave Hargis, Microsoft Access MVP


Maarkr said:
that answers that...I'll delete the code and try something else...thx

Klatuu said:
There are a number of problems here.
Going to the last record in a recordset will not necessarily, in fact
probably will not, take you to the last record you entered. There is no set
order of records in a table. Each new record is put whereever is convenient
at the moment. And, last record depends on whatever sort order is currently
in place. Therefore, you will need to come up with a scheme to determine the
most recently entere record. If you have an autonumber field and you have
the autonumber set to increment, the highest number in that field would be
the most recently entered or you can add a date/time stamp field and populate
in when you create a new record.

Also, you should not be hard coding peoples' names. Just a bad practive.

And last, Data Entry mode for a form only allows adding new records. You
will not even be able to see existing records.
--
Dave Hargis, Microsoft Access MVP


:

Excellent point on the design...in my case, the table is already filled with
some personal info (name, phone, address) for a thousand people and I want
each person to privately open up their partial record and fill in the rest
of the field data. I 'm working on several dbs at once and get my questions
mixed. I had put an unbound combo box in but didn't set the query up
correctly...doh!...so in one case, I didn't need to populate anything, just
have the unbdcbo open up the pqry.

BUT, in my other case, I want to open the form in data entry or Add mode and
run this code that works fine in edit mode. It looks at the last record to
see who the InitRecr is and displays the next InitRecr in the Text208
unbdtxtbox.

DoCmd.GoToRecord acForm, "frmReferral", acLast
If [InitRecr] = "John" Then
DoCmd.GoToRecord , , acNewRec
[Text208] = "Carol"
Else
If [InitRecr] = "Carol" Then
DoCmd.GoToRecord , , acNewRec
[Text208] = "Van"
Else
If [InitRecr] = "Van" Then
DoCmd.GoToRecord , , acNewRec
[Text208] = "John"
Else
End If
End If
End If

Sorry for the confusion and thx..

:

The better way to do this would be to put an unbound combo box on your form.
Make its record source a query that returns the fields you want to populate.
Use the combo's After Update event to populate the fields.

Now, I do question your database design. If you already have the person's
name and SSN, you should not be duplicating it in another table. What you
should do is have a field in this table that would store the foreign key to
the table when the person's info already is.
--
Dave Hargis, Microsoft Access MVP


:

I would like to have the users open up a form in Add mode and populate some
of the fields from a parameter query. My intent is to use a parameter query
to match their last name and last 4 of their ssn to fill in some basic
personal info on the form, then they fill out the rest. I don't want them to
have access to all records like you get when you just open a form in edit
mode. Maybe you have some other ideas on how to do that? I haven't tried
coding a lookup or recordset to fill it in yet...just hoping to save time by
asking for suggestions.
Click to expand...
Click to expand...
Click to expand...
 
Top