New Record Update

  • Thread starter Thread starter Michelle
  • Start date Start date
M

Michelle

How do I update a table when I enter a new record.
Everytime, I enter a new record with the autofill
function. Although, I can see it in my form, the new
entry does not show in my table and reports.

Any ideas!

Thanks

Michelle
 
Michelle,

If by "Auto Fill" you mean "Default Value", you are correct. Setting a
Default Value will not make the record "dirty". You have to change at least
one field manually to make the record dirty so that Access will save it.
Another option would be a Save Record button in which you use code to make
the record dirty (Me.Dirty = True) then save the record.

This assumes that this is a bound form (there is a Record Source defined for
the form).
 
Allo Wayne,
First you need to excuse me for I am french and sometimes
I do not express myself well. Anyhow, I have set my
autofill fields in my form (first). I have a field with a
combobox that allow me to choose an account. Once I pick
the account, I have two other fields that automatically
filled with the name and location. Now it is working
wonderfully, the problem is that when I enter a new record
and choose a new account, it does not show up in my table
therefore neither in my report. Because I have learn
everything by myself (and I am really not too bad for a
rookie), I have maybe forgot some things. I have set the
account field in my table to lookup wizard and the 2 other
ones to text. Now eventhough I go back and drop them in
my form, it is still not working.

I really hope you can understand what I am explaining. I
have this problem since awhile now and I am trying very
hard to resolve it on my own, but I am afraid I need
someone with greater experience.

Thank you for all your help again.

Michelle
 
Ok, assuming that the form and controls are bound to the table, then making
a selection and saving the record should write to the table. To save the
record you have to either explicitly save it or move off of that record to
another record and Access will automatically save it. Are you making the
selections then immediately opening the report? If you close the form and
reopen it does the new entry show up?
 
Good Morning Wayne,

I have saved it and close the form and view my reports, it
is still remaining the same. (No data showing). However,
my two fields that are autofilling have this: =
[UIC].Column(1) and [UIC].Column(2)in the control source
area. My first field (the one with the ComboBox) is bound
to UIC, but not the 2 other one.

I have to explain that when I started designing the form
at the begining, the field called UIC and the 2 other ones
were plain text, which means that I had to write them
down. After reviewing it and thinking about it I decided
to put an autofill section to make it easier for a data
entry clerk. However, it is since I have made that change
that everytime I add a new record and choose a new option
(from UIC), it doesn't show up anywhere besides my form.

Thank you for taking the time to help me on this. I am
scheduled to handover pretty soon this database to a new
employee, but with this problem I am afraid that the new
employee won't be able to give out the proper information
in reports.

Merci encore de ton aide

Michelle
 
Ok, I'm having trouble following you, so I need the following answered
explicitly.

1) What is the name of the table that the data should be in?

2) What is the name of each field that the data should be in?

3) What is the name of the form?

4) If the form is a subform, what is the name of its parent form?

5) If the form is a subform, what is the name of the subform control on the
parent form?

6) What type of control is each of the fields bound to?

7) What is the name of each of these controls?

8) If the control(s) is a combo box
a) What is its Control Source?
b) What is its Row Source Type?
c) What is its Row Source? If a query, paste in the SQL of the query.
d) What is its Bound Column?
e) What is its Column Count?

9) What is the form's Record Source? If a query, paste in the SQL of the
query.

10) What is the data type of each field (from the main table and the one
supplying the Row Source of the combo box).

--
Wayne Morgan
MS Access MVP


Michelle said:
Good Morning Wayne,

I have saved it and close the form and view my reports, it
is still remaining the same. (No data showing). However,
my two fields that are autofilling have this: =
[UIC].Column(1) and [UIC].Column(2)in the control source
area. My first field (the one with the ComboBox) is bound
to UIC, but not the 2 other one.

I have to explain that when I started designing the form
at the begining, the field called UIC and the 2 other ones
were plain text, which means that I had to write them
down. After reviewing it and thinking about it I decided
to put an autofill section to make it easier for a data
entry clerk. However, it is since I have made that change
that everytime I add a new record and choose a new option
(from UIC), it doesn't show up anywhere besides my form.

Thank you for taking the time to help me on this. I am
scheduled to handover pretty soon this database to a new
employee, but with this problem I am afraid that the new
employee won't be able to give out the proper information
in reports.

Merci encore de ton aide

Michelle
-----Original Message-----
Ok, assuming that the form and controls are bound to the table, then making
a selection and saving the record should write to the table. To save the
record you have to either explicitly save it or move off of that record to
another record and Access will automatically save it. Are you making the
selections then immediately opening the report? If you close the form and
reopen it does the new entry show up?

--
Wayne Morgan
MS Access MVP





.
Click to expand...
Click to expand...
 
Good morning Wayne,

Here is below all my answers. Wow! what a fantastic help
that you provide. Thanks again.

Michelle
-----Original Message-----
Ok, I'm having trouble following you, so I need the following answered
explicitly.

1) What is the name of the table that the data should be
Click to expand...
in? NAME OF TABLE: HONORARY APPOINTMENTS AND EXTEN
2) What is the name of each field that the data should be
Click to expand...
in? FIELD 1: UIC FIELD 2: UNIT NAME FIELD 3: UNIT
LOCATION
3) What is the name of the form? HONORARY APPOINTMENTS AND EXTEN QUERY
4) If the form is a subform, what is the name of its parent form? NO SUBFORM

5) If the form is a subform, what is the name of the subform control on the
parent form?

6) What type of control is each of the fields bound to?
FIELD 1: ComboBox Field 2: TextBox Field 3: TextBox
7) What is the name of each of these controls?
FIELD 1: UIC FIELD 2: UNIT NAME FIELD 3: UNIT LOCATION
8) If the control(s) is a combo box
a) What is its Control Source? UIC
b) What is its Row Source Type?UIC QUERY
c) What is its Row Source? If a query, paste in the
Click to expand...
SQL of the query. SELECT UIC.UIC, UIC.[Unit Name], UIC.
[Unit Location]
FROM UIC
ORDER BY UIC.UIC;
d) What is its Bound Column? 1
e) What is its Column Count? 3

9) What is the form's Record Source? If a query, paste in the SQL of the
query. HONORARY APPOINTMENTS AND EXTEN

10) What is the data type of each field (from the main table and the one
supplying the Row Source of the combo box).
Click to expand...
FIELD 1: UIC - DATA TYPE IS NUMBER. ROW SOURCE IS SELECT
[UIC Query].[UIC], [UIC Query].[Unit Name], [UIC Query].
[Unit Location] FROM [UIC Query];

FIELD 2: UNIT NAME DATA TYPE IS TEXT
FIELD 3: UNIT LOCATION DATA TYPE IS TEXT
--
Wayne Morgan
MS Access MVP


Good Morning Wayne,

I have saved it and close the form and view my reports, it
is still remaining the same. (No data showing). However,
my two fields that are autofilling have this: =
[UIC].Column(1) and [UIC].Column(2)in the control source
area. My first field (the one with the ComboBox) is bound
to UIC, but not the 2 other one.

I have to explain that when I started designing the form
at the begining, the field called UIC and the 2 other ones
were plain text, which means that I had to write them
down. After reviewing it and thinking about it I decided
to put an autofill section to make it easier for a data
entry clerk. However, it is since I have made that change
that everytime I add a new record and choose a new option
(from UIC), it doesn't show up anywhere besides my form.

Thank you for taking the time to help me on this. I am
scheduled to handover pretty soon this database to a new
employee, but with this problem I am afraid that the new
employee won't be able to give out the proper information
in reports.

Merci encore de ton aide

Michelle
-----Original Message-----
Ok, assuming that the form and controls are bound to the table, then making
a selection and saving the record should write to the table. To save the
record you have to either explicitly save it or move off of that record to
another record and Access will automatically save it.
Click to expand...
Are
you making the
selections then immediately opening the report? If you close the form and
reopen it does the new entry show up?

--
Wayne Morgan
MS Access MVP


Allo Wayne,
First you need to excuse me for I am french and sometimes
I do not express myself well. Anyhow, I have set my
autofill fields in my form (first). I have a field with a
combobox that allow me to choose an account. Once I pick
the account, I have two other fields that automatically
filled with the name and location. Now it is working
wonderfully, the problem is that when I enter a new record
and choose a new account, it does not show up in my table
therefore neither in my report. Because I have learn
everything by myself (and I am really not too bad for a
rookie), I have maybe forgot some things. I have set the
account field in my table to lookup wizard and the 2 other
ones to text. Now eventhough I go back and drop them in
my form, it is still not working.

I really hope you can understand what I am explaining. I
have this problem since awhile now and I am trying very
hard to resolve it on my own, but I am afraid I need
someone with greater experience.

Thank you for all your help again.

Michelle

-----Original Message-----
Michelle,

If by "Auto Fill" you mean "Default Value", you are
correct. Setting a
Default Value will not make the record "dirty". You have
to change at least
one field manually to make the record dirty so that
Access will save it.
Another option would be a Save Record button in which you
use code to make
the record dirty (Me.Dirty = True) then save the record.

This assumes that this is a bound form (there is a Record
Source defined for
the form).

--
Wayne Morgan
MS Access MVP


message
How do I update a table when I enter a new record.
Everytime, I enter a new record with the autofill
function. Although, I can see it in my form, the new
entry does not show in my table and reports.


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Ok, judging from what you've given me, you've made the correct move, it just
needs a little more work.

The "lookup table" for the combo box appears to be UIC, that is the table
you are getting the values from for the Row Source of the combo box. The
value of the bound column is UIC.UIC. This table will be stored in the
form's Record Source table "Honorary Appointments and Exten". Prior to your
change, you were also typing in the two textboxes the values that were in
the other two columns of the combo box and storing that data in the
"Honorary Appointments and Exten" table also.

It is not necessary to store all three values if the UIC field in the UIC
table contains unique values (no duplicate values in that field). The field
should be set as the "primary key" for the UIC table. To get the two text
values into the report, you would use a query for the report. In the query,
place both the "Honorary Appointments and Exten" and UIC tables. Drag the
UIC field from one table and drop it on the UIC field of the other table.
This will link the two tables on that field. In the query, add the field
from "Honorary Appointments and Exten" that you want in the report and also
add the two text field from the UIC table. The UIC field will act as a
"pointer" or "link" to the correct record with the associated text in the
UIC table.

Once this is done, what you should find is that the value for UIC is being
stored in the "Honorary Appointments and Exten" table, but not the two text
values. However, you can retrieve the associated text values using the
query.

--
Wayne Morgan
Microsoft Access MVP


Michelle said:
Good morning Wayne,

Here is below all my answers. Wow! what a fantastic help
that you provide. Thanks again.

Michelle
-----Original Message-----
Ok, I'm having trouble following you, so I need the following answered
explicitly.

1) What is the name of the table that the data should be
Click to expand...
in? NAME OF TABLE: HONORARY APPOINTMENTS AND EXTEN
2) What is the name of each field that the data should be
Click to expand...
in? FIELD 1: UIC FIELD 2: UNIT NAME FIELD 3: UNIT
LOCATION
3) What is the name of the form? HONORARY APPOINTMENTS AND EXTEN QUERY
4) If the form is a subform, what is the name of its parent form? NO SUBFORM

5) If the form is a subform, what is the name of the subform control on the
parent form?

6) What type of control is each of the fields bound to?
FIELD 1: ComboBox Field 2: TextBox Field 3: TextBox
7) What is the name of each of these controls?
FIELD 1: UIC FIELD 2: UNIT NAME FIELD 3: UNIT LOCATION
8) If the control(s) is a combo box
a) What is its Control Source? UIC
b) What is its Row Source Type?UIC QUERY
c) What is its Row Source? If a query, paste in the
Click to expand...
SQL of the query. SELECT UIC.UIC, UIC.[Unit Name], UIC.
[Unit Location]
FROM UIC
ORDER BY UIC.UIC;
d) What is its Bound Column? 1
e) What is its Column Count? 3

9) What is the form's Record Source? If a query, paste in the SQL of the
query. HONORARY APPOINTMENTS AND EXTEN

10) What is the data type of each field (from the main table and the one
supplying the Row Source of the combo box).
Click to expand...
FIELD 1: UIC - DATA TYPE IS NUMBER. ROW SOURCE IS SELECT
[UIC Query].[UIC], [UIC Query].[Unit Name], [UIC Query].
[Unit Location] FROM [UIC Query];

FIELD 2: UNIT NAME DATA TYPE IS TEXT
FIELD 3: UNIT LOCATION DATA TYPE IS TEXT
--
Wayne Morgan
MS Access MVP


Good Morning Wayne,

I have saved it and close the form and view my reports, it
is still remaining the same. (No data showing). However,
my two fields that are autofilling have this: =
[UIC].Column(1) and [UIC].Column(2)in the control source
area. My first field (the one with the ComboBox) is bound
to UIC, but not the 2 other one.

I have to explain that when I started designing the form
at the begining, the field called UIC and the 2 other ones
were plain text, which means that I had to write them
down. After reviewing it and thinking about it I decided
to put an autofill section to make it easier for a data
entry clerk. However, it is since I have made that change
that everytime I add a new record and choose a new option
(from UIC), it doesn't show up anywhere besides my form.

Thank you for taking the time to help me on this. I am
scheduled to handover pretty soon this database to a new
employee, but with this problem I am afraid that the new
employee won't be able to give out the proper information
in reports.

Merci encore de ton aide

Michelle
-----Original Message-----
Ok, assuming that the form and controls are bound to the
table, then making
a selection and saving the record should write to the
table. To save the
record you have to either explicitly save it or move off
of that record to
another record and Access will automatically save it. Are
you making the
selections then immediately opening the report? If you
close the form and
reopen it does the new entry show up?

--
Wayne Morgan
MS Access MVP


Allo Wayne,
First you need to excuse me for I am french and
sometimes
I do not express myself well. Anyhow, I have set my
autofill fields in my form (first). I have a field
with a
combobox that allow me to choose an account. Once I
pick
the account, I have two other fields that automatically
filled with the name and location. Now it is working
wonderfully, the problem is that when I enter a new
record
and choose a new account, it does not show up in my
table
therefore neither in my report. Because I have learn
everything by myself (and I am really not too bad for a
rookie), I have maybe forgot some things. I have set
the
account field in my table to lookup wizard and the 2
other
ones to text. Now eventhough I go back and drop them in
my form, it is still not working.

I really hope you can understand what I am explaining.
I
have this problem since awhile now and I am trying very
hard to resolve it on my own, but I am afraid I need
someone with greater experience.

Thank you for all your help again.

Michelle

-----Original Message-----
Michelle,

If by "Auto Fill" you mean "Default Value", you are
correct. Setting a
Default Value will not make the record "dirty". You have
to change at least
one field manually to make the record dirty so that
Access will save it.
Another option would be a Save Record button in which
you
use code to make
the record dirty (Me.Dirty = True) then save the record.

This assumes that this is a bound form (there is a
Record
Source defined for
the form).

--
Wayne Morgan
MS Access MVP


in
message
How do I update a table when I enter a new record.
Everytime, I enter a new record with the autofill
function. Although, I can see it in my form, the new
entry does not show in my table and reports.


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top