Macro to Fill Down, or Fill Right

[ryguy7272]

I am trying to find an Access macro that will fill down, with a string such
as 'Class' if there is a null in a certain Column, in Field 'Type'. Also, in
this macro, I want to test to see if a value is filled in in a different
Column, in Field 'Related Company', and if there is a null in this Column,
copy the value one Column from the left, which is Field 'Company'. How can
this be done? I've seen a fill down macro before, but I can't find it now.
I don't know for sure if Access can fill a null, if the cell to the left is
non null.

I'd appreciate any help!!

Regards,
Ryan---

 

[pietlinden]

I am trying to find an Access macro that will fill down, with a string such
as 'Class' if there is a null in a certain Column, in Field 'Type'.  Also, in
this macro, I want to test to see if a value is filled in in a different
Column, in Field 'Related Company', and if there is a null in this Column,
copy the value one Column from the left, which is Field 'Company'.  How can
this be done?  I've seen a fill down macro before, but I can't find it now.  
I don't know for sure if Access can fill a null, if the cell to the left is
non null.

I'd appreciate any help!!

Regards,
Ryan---

Access != Excel
Copying *accross* in Access is rarely a good thing. sounds like a non-
normalized design, because you have two of the same type of entity
being described in the same table. (Logical one-to-many
relationship). Copying data from a previous record is more normal.
There's a nice example on www.mvps.org...

This is it...

(Q) How do I carry forward the current value of a control so that
it's automatically entered for all new records?

(A) To use the curent control value for new records, you need to
assign it to the defaultvalue of the control. For example something
like

'******** Code Start **********
const cQuote="""" 'Thats two quotes
me!Control.DefaultValue = cQuote & me!Control.Value & cQuote
'******** Code End **********

would carry the current value of the control forward if you place
this code behind a command button to conditionally run it.

Here's the link:
http://www.mvps.org/access/forms/frm0012.htm

 

[ryguy7272]

I just realized a simple Update Query helped me solve my first problem (I
recollect using a macro for this before). Anyway, I think the second part of
the task is much more difficult. I don't think an Update Query will allow me
to do what I want to do, which as I described before is:
Check for null in Column named 'Related Company'.
If null is found, copy/paste value to the left, which is Column named
'Company'.

How can this be done?

Regards,
Ryan---

 

[fredg]

I just realized a simple Update Query helped me solve my first problem (I
recollect using a macro for this before). Anyway, I think the second part of
the task is much more difficult. I don't think an Update Query will allow me
to do what I want to do, which as I described before is:
Check for null in Column named 'Related Company'.
If null is found, copy/paste value to the left, which is Column named
'Company'.

How can this be done?

Regards,
Ryan---

Back up your table.

Run an Update query.
Update YourTable Set YourTable.[RelatedCompany] = [Company] Where
YourTable.[RelatedCompany] is null;

But why bother. It's not a good idea to have duplicate data stored in
a table. If there is no [RelatedCompany] leave it blank.
Any time you need to enter the [RelatedCompany] value in a report or
on a form, you can simply use an IIf expression, using an unbound
control, to enter the [Company] if the [RelatedCompany] is null.

=IIf(IsNull([RelatedCompany]),[Company],[RelatedCompany])

 

[ryguy7272]

Thanks a lot! I went with fredg's recommendation. Yes, I know, eliminate
redundancies in databases, but I wanted to update this table for an eventual
export to Excel and I need all cells in Excel filled for certain operations.

Regards,
ryan----