Populate Unbound Table Feild from an unrelated Form

[Guest]

Bl**dy new users

HI all, thanks for all help in the past. I will try to explain as follows

I have a main form, MAIN_DATA_ENTRY
There is a second page to the form which carries a subform, I also have
Tbl_Main_Data and the sub form is linked through a query to Tbl_Dev_Plan
linked by unique key fields with update referential integrity.

The main form generates a record which is demonstrated on its table which
has a + against it showing the is a related sub form/table record is present.
However, all fields are null. My Second Page subform generates a bar graph
based on the contents of a bound record source on the same form. In order to
show real time, the bar graph, the form runs Macros (I intend to re-write the
macros in VBA when I am more proficient) on open to calculate the length of
the bar.

Everthing works well if there is data in any of the fields in the sub_table
but everything throws and error if all fields are null.

Question, can I force one of the sub_table fields on which the calculations
are performed to 0 (Zero) Value?

I can do this mechanicall by typing a record to a field in the sub form at
which point there is a record generated and everything caculates without
error.

My Table would be [Tbl_Dev_Plan] and the field would be [Prog_1)

I cant seem to get the syntax right to add a record to the unbound subtable
from the front form using VBA, I can change all existing record values to a
new value so I am missing how to force a new record to be added. I have tried
the following code

DoCmd.RunSQL "UPDATE Tbl_Dev_Plan Set
Tbl_Dev_Plan.Prog_1='[Tbl_Dev_Plan],[Prog_1]+1'=0"
'[Tbl_Dev_Plan].[Prog_1]+1= "

Which of course updates all the existing values

Can anyone help please?

 

[Ron Hinds]

In Design View you can set a Default Value for any field. That value will
automatically be inserted anytime a new record is added and a value is not
explicitly set for that field. Open the table in Design View, put the cursor
in the field you want to set a default for, and put the value (0 in this
case) into the Default Value field under Properties.

As for your SQL statement - if you want to ADD a record, the format is:

DoCmd.RunSQL "INSERT INTO table_name (field1_name, field2_name, ...
fieldN_name) VALUES (value1, value2, ... valueN)

The number of fields and their order in the first set of parentheses must
match exactly in the second set.

 

[Guest]

Thanks Ron, your answer is exactly what I was looking for. I am picking this
up slowly, will try to see if there any good VBA books about. I had set the
default value in the table but although that then shows a 0 value, it doesn't
force a value into a field to generate a real record in the subform. Your
advice when applied from my main form works great. Thanks for your help.

Cheers

In Design View you can set a Default Value for any field. That value will
automatically be inserted anytime a new record is added and a value is not
explicitly set for that field. Open the table in Design View, put the cursor
in the field you want to set a default for, and put the value (0 in this
case) into the Default Value field under Properties.

As for your SQL statement - if you want to ADD a record, the format is:

DoCmd.RunSQL "INSERT INTO table_name (field1_name, field2_name, ...
fieldN_name) VALUES (value1, value2, ... valueN)

The number of fields and their order in the first set of parentheses must
match exactly in the second set.

Bl**dy new users

HI all, thanks for all help in the past. I will try to explain as follows

I have a main form, MAIN_DATA_ENTRY
There is a second page to the form which carries a subform, I also have
Tbl_Main_Data and the sub form is linked through a query to Tbl_Dev_Plan
linked by unique key fields with update referential integrity.

The main form generates a record which is demonstrated on its table which
has a + against it showing the is a related sub form/table record is present.
However, all fields are null. My Second Page subform generates a bar graph
based on the contents of a bound record source on the same form. In order to
show real time, the bar graph, the form runs Macros (I intend to re-write the
macros in VBA when I am more proficient) on open to calculate the length of
the bar.

Everthing works well if there is data in any of the fields in the sub_table
but everything throws and error if all fields are null.

Question, can I force one of the sub_table fields on which the calculations
are performed to 0 (Zero) Value?

I can do this mechanicall by typing a record to a field in the sub form at
which point there is a record generated and everything caculates without
error.

My Table would be [Tbl_Dev_Plan] and the field would be [Prog_1)

I cant seem to get the syntax right to add a record to the unbound subtable
from the front form using VBA, I can change all existing record values to a
new value so I am missing how to force a new record to be added. I have tried
the following code

DoCmd.RunSQL "UPDATE Tbl_Dev_Plan Set
Tbl_Dev_Plan.Prog_1='[Tbl_Dev_Plan],[Prog_1]+1'=0"
'[Tbl_Dev_Plan].[Prog_1]+1= "

Which of course updates all the existing values

Can anyone help please?

 

[Ron Hinds]

Your welcome! You'd probably be better off with a good Access book. You
didn't mention what version you are using but take a look at the books by
Litwin and Getz - these guys have written Access books for several versions
(titled Acess xxx Developer's Handbook where xxx is the version) and they
are an excellent place to start.

Thanks Ron, your answer is exactly what I was looking for. I am picking this
up slowly, will try to see if there any good VBA books about. I had set the
default value in the table but although that then shows a 0 value, it doesn't
force a value into a field to generate a real record in the subform. Your
advice when applied from my main form works great. Thanks for your help.

Cheers

In Design View you can set a Default Value for any field. That value will
automatically be inserted anytime a new record is added and a value is not
explicitly set for that field. Open the table in Design View, put the cursor
in the field you want to set a default for, and put the value (0 in this
case) into the Default Value field under Properties.

As for your SQL statement - if you want to ADD a record, the format is:

DoCmd.RunSQL "INSERT INTO table_name (field1_name, field2_name, ...
fieldN_name) VALUES (value1, value2, ... valueN)

The number of fields and their order in the first set of parentheses must
match exactly in the second set.

Bl**dy new users

HI all, thanks for all help in the past. I will try to explain as follows

I have a main form, MAIN_DATA_ENTRY
There is a second page to the form which carries a subform, I also have
Tbl_Main_Data and the sub form is linked through a query to Tbl_Dev_Plan
linked by unique key fields with update referential integrity.

The main form generates a record which is demonstrated on its table which
has a + against it showing the is a related sub form/table record is present.
However, all fields are null. My Second Page subform generates a bar graph
based on the contents of a bound record source on the same form. In

order
to

show real time, the bar graph, the form runs Macros (I intend to

re-write
the

macros in VBA when I am more proficient) on open to calculate the

length
of

the bar.

Everthing works well if there is data in any of the fields in the sub_table
but everything throws and error if all fields are null.

Question, can I force one of the sub_table fields on which the calculations
are performed to 0 (Zero) Value?

I can do this mechanicall by typing a record to a field in the sub form at
which point there is a record generated and everything caculates without
error.

My Table would be [Tbl_Dev_Plan] and the field would be [Prog_1)

I cant seem to get the syntax right to add a record to the unbound subtable
from the front form using VBA, I can change all existing record values

to
a

new value so I am missing how to force a new record to be added. I

have
tried

the following code

DoCmd.RunSQL "UPDATE Tbl_Dev_Plan Set
Tbl_Dev_Plan.Prog_1='[Tbl_Dev_Plan],[Prog_1]+1'=0"
'[Tbl_Dev_Plan].[Prog_1]+1= "

Which of course updates all the existing values

Can anyone help please?