Do I need to solve this with VBA record set?

[AltaEgo]

Hi

I have a need to populate zero value for selected fields in a table when
using a sub form.

Main table = DailyStats
Sub table = Rejections
Link = StatID

Rejections has about 6 reasons for rejection. These will change over time.

Rejections has three fields
StatID
Reason
Required (Y/N) field


When entering data, I need to ensure all fields in Rejections with a
Required value of yes are populated with a value (i.e. a number >=zero). All
other values should be null.

I have in mind the sub form creating a line for each required value when a
new DailyStats record is added. Do I need to create a record set or is there
another solution?

 

[Allen Browne]

Steve, I think you are saying that Rejections.Reason must have an entry when
Rejections.Required is True, and must be blank when Rejections.Required is
False?

If so, you can do that with a validation rule on the table:
1. Open the table in Design View.
2. Open the Properties box.
3. Beside the Validation Rule in the Properties box (not the one in the
lower pane of table design which only applies to one field), enter:
([Required]) XOR ([Reason] Is Null)

XOR is the exclusive OR operator, so one or the other must be true (but not
both.)

More about using validation rules in general:
http://allenbrowne.com/ValidationRule.html

 

[AltaEgo]

Allen

Not quite. What I need is a list or records in the sub when a new record is
added to the main. This sub should populate only records that are required.
Other records should not show.

Thank you for the link on validation rules. Its been about eight years and
the refresh will be very useful in the impossible six days left to complete
this project...Wonder if anyone knows where to find surplus midnight oil )


--
Steve

Steve, I think you are saying that Rejections.Reason must have an entry
when Rejections.Required is True, and must be blank when
Rejections.Required is False?

If so, you can do that with a validation rule on the table:
1. Open the table in Design View.
2. Open the Properties box.
3. Beside the Validation Rule in the Properties box (not the one in the
lower pane of table design which only applies to one field), enter:
([Required]) XOR ([Reason] Is Null)

XOR is the exclusive OR operator, so one or the other must be true (but
not both.)

More about using validation rules in general:
http://allenbrowne.com/ValidationRule.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a need to populate zero value for selected fields in a table when
using a sub form.

Main table = DailyStats
Sub table = Rejections
Link = StatID

Rejections has about 6 reasons for rejection. These will change over
time.

Rejections has three fields
StatID
Reason
Required (Y/N) field


When entering data, I need to ensure all fields in Rejections with a
Required value of yes are populated with a value (i.e. a number >=zero).
All other values should be null.

I have in mind the sub form creating a line for each required value when
a new DailyStats record is added. Do I need to create a record set or is
there another solution?

 

[Allen Browne]

So the subform should show only records where teh [Required] field is True?

Create a query.
Enter True in the Criteria row under [Required].
Use the query as the RecordSource for your subform.

 

[AltaEgo]

Correct. The should only show a line where there the [Required] field is
True and it should show every line where [Required] is True.

The aim is users will enter values where this is relevant, the rest will
store the default zero value. In other words, I need some user entered
values, some zero values and some null values. Rather than have users select
[Reason] then enter a value, I want all required fields showing and the user
over-writes the zero value when relevant.

--
Steve

So the subform should show only records where teh [Required] field is
True?

Create a query.
Enter True in the Criteria row under [Required].
Use the query as the RecordSource for your subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen

Not quite. What I need is a list or records in the sub when a new record
is added to the main. This sub should populate only records that are
required. Other records should not show.

 

[Allen Browne]

Not sure I'm understanding your goal.

If you just want to clear (or zero) the field named Required when the user
enters a Reason, could you not do that in the AfterUpdate event procedure of
the Reason text box?

 

[AltaEgo]

I want the user to not select reason. The sub should automatically fill with
every record where [Required] = True.

Example
Either when first opened or after data entered into the main form, the
record should look like this

Main form
ID; Date; machine; start number; finish number
Sub form
Reason; Tally
A ; 0
B ; 0
D ; 0
F ; 0


In the above, 'C' and 'E' are [Required] = False.

Back to the original Q. Do I need to create a recordset to solve the
problem? I am a little rusty but can do this. What I wish to know is whether
there is an alternative method.

--
Steve

Not sure I'm understanding your goal.

If you just want to clear (or zero) the field named Required when the user
enters a Reason, could you not do that in the AfterUpdate event procedure
of the Reason text box?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Correct. The should only show a line where there the [Required] field is
True and it should show every line where [Required] is True.

The aim is users will enter values where this is relevant, the rest will
store the default zero value. In other words, I need some user entered
values, some zero values and some null values. Rather than have users
select [Reason] then enter a value, I want all required fields showing
and the user over-writes the zero value when relevant.

 

[Allen Browne]

Sorry, Steve: perhaps someone else understands what you are trying to do.

I don't understand where the subform records came from. If the user does not
enter them, perhaps you are trying to enter them programmatically in
Form_AfterInsert. If so, you may have another table that defines the 6
default records of your example, and if so, you could do that by exeucting
an Append query rather than open a recordset. (Then requery so they show up
in the subform, without the C and E which are presumably excluded by the
subform's query.)

Or perhaps another reader will reply with a response to what you need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I want the user to not select reason. The sub should automatically fill
with every record where [Required] = True.

Example
Either when first opened or after data entered into the main form, the
record should look like this

Main form
ID; Date; machine; start number; finish number
Sub form
Reason; Tally
A ; 0
B ; 0
D ; 0
F ; 0


In the above, 'C' and 'E' are [Required] = False.

Back to the original Q. Do I need to create a recordset to solve the
problem? I am a little rusty but can do this. What I wish to know is
whether there is an alternative method.

--
Steve

Not sure I'm understanding your goal.

If you just want to clear (or zero) the field named Required when the
user enters a Reason, could you not do that in the AfterUpdate event
procedure of the Reason text box?

Correct. The should only show a line where there the [Required] field is
True and it should show every line where [Required] is True.

The aim is users will enter values where this is relevant, the rest will
store the default zero value. In other words, I need some user entered
values, some zero values and some null values. Rather than have users
select [Reason] then enter a value, I want all required fields showing
and the user over-writes the zero value when relevant.

 

[AltaEgo]

Allen

My humble apologies. I unintentionally mislead you. I should have read the
question while checking the form but am writing from home where I do not
have a copy. One of the fields in the sub is a lookup. This is the field
with [ Required].

If I read you correctly, after adding a new line in the main, I run an
append to the sub using the ID from the main? After this, I requery the sub?

--
Steve

Sorry, Steve: perhaps someone else understands what you are trying to do.

I don't understand where the subform records came from. If the user does
not enter them, perhaps you are trying to enter them programmatically in
Form_AfterInsert. If so, you may have another table that defines the 6
default records of your example, and if so, you could do that by exeucting
an Append query rather than open a recordset. (Then requery so they show
up in the subform, without the C and E which are presumably excluded by
the subform's query.)

Or perhaps another reader will reply with a response to what you need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I want the user to not select reason. The sub should automatically fill
with every record where [Required] = True.

Example
Either when first opened or after data entered into the main form, the
record should look like this

Main form
ID; Date; machine; start number; finish number
Sub form
Reason; Tally
A ; 0
B ; 0
D ; 0
F ; 0


In the above, 'C' and 'E' are [Required] = False.

Back to the original Q. Do I need to create a recordset to solve the
problem? I am a little rusty but can do this. What I wish to know is
whether there is an alternative method.

--
Steve

Not sure I'm understanding your goal.

If you just want to clear (or zero) the field named Required when the
user enters a Reason, could you not do that in the AfterUpdate event
procedure of the Reason text box?

Correct. The should only show a line where there the [Required] field
is True and it should show every line where [Required] is True.

The aim is users will enter values where this is relevant, the rest
will store the default zero value. In other words, I need some user
entered values, some zero values and some null values. Rather than have
users select [Reason] then enter a value, I want all required fields
showing and the user over-writes the zero value when relevant.

 

[Allen Browne]

If I read you correctly, after adding a new line in the main, I run an
append to the sub using the ID from the main? After this, I requery the
sub?

Yes: this kind of thing:

Private Sub Form_AfterInsert()
Dim strSql As String
strSql = "INSERT INTO ...
dbEngine(0)(0).Execute strSql, dbFailOnError
Me.[Sub1].Form.Requery
End Sub

 

[AltaEgo]

Thank you.

--
Steve

If I read you correctly, after adding a new line in the main, I run an
append to the sub using the ID from the main? After this, I requery the
sub?

Yes: this kind of thing:

Private Sub Form_AfterInsert()
Dim strSql As String
strSql = "INSERT INTO ...
dbEngine(0)(0).Execute strSql, dbFailOnError
Me.[Sub1].Form.Requery
End Sub

 

[AltaEgo]

I ran up an example. and it is working as required.

--
Steve

Thank you.

--
Steve

If I read you correctly, after adding a new line in the main, I run an
append to the sub using the ID from the main? After this, I requery the
sub?

Yes: this kind of thing:

Private Sub Form_AfterInsert()
Dim strSql As String
strSql = "INSERT INTO ...
dbEngine(0)(0).Execute strSql, dbFailOnError
Me.[Sub1].Form.Requery
End Sub