Adding Data from Form to Table depending on blank field in Table 1

[boredguy2000]

Hello all.
I am trying to write data from 2 form fields to a table based on whether this is the first time or subsequent time it has been written.

Something like:

Private Sub Capture_Status_Change_Click()

If Table![workingtable].[LMStatus1] Is Null Then
Let Table![workingtable].[LMStatus1] = Me.[Loss Mit Status]
Let Table![workingtable].[ApprovalDate1] = Me.[Approval Date]

Else
Let Table![workingtable].[LMStatus2] = Me.[Loss Mit Status]
Let Table![workingtable].[ApprovalDate2] = Me.[Approval Date]

End If
(I get an error w/ this code saying "Object Required")

I am not sure if my point makes sense but will surely be checking every other second and will clarify if needed. Thanks guys!!!

 

[boredguy2000]

I'm sorry for this but the one aspect not addressed was my question:
2) Just from an advice standpoint: I should be able to recreate this "plan" by just nesting IF's down (i.e. LMStatus3, LMStatus4 etc.) right?

I'm just not positive how the test AFTER LMStatus2, going on to write to LMStatus3 should look?

The last code provided by PHV was amazingly helpful but I just need a little direction how to go about modifying to multiple (LMStatus3, 4 etc.)

 

[Golom]

I should be able to recreate this "plan" by just nesting IF's down (i.e. LMStatus3, LMStatus4 etc.) right?

Short answer ... Yes.

Somewhat longer answer ... You have an un-normalized table there and it will create no end of headaches for you. Any time you have repeating fields in a table like LMStatus1, LMStatus2, LMStatus3, LMStatus4, ... that is a violation of first normal form.

You should read Fundamentals of Relational Database Design if that doesn't make any sense to you.

 

[boredguy2000]

It actually does make sense and I have designed db's in the past that have certainly been more "normal". At this point though I really just want to get past this facet and move on.

The question really comes in how to incorporate the nested if, that is the condition between LMStatus1 and LMStatus2 and especially the next "instance".

Goloms' and PHVs' code help has been great it is just now that PHV supplied:
If Trim(rs![LMStatus1] & "") = "" Then
not using the ISNull that I am used to I am uncertain with how to proceed with the nested IF design.

Thank you both/all again!

 

[Golom]

To me the issue is that the test you are doing is binary (either it is or is not null). With PHV's help you are testing
[tt]
- If it is NULL or an empty string <--- TRUE
- Otherwise <--- FALSE
[/tt]

Now you want to make a decision that has more than two outcomes (i.e. LMStatus1, LMStatus2, LMStatus3, LMStatus4, etc.) I just don't see any way to do that when you have only two possible outcomes for your test.

You probably need some second criteria that you can test to determine which of the LMStatus fields to update.

It may be as simple as

If Trim$(LMStatus1 & "") = "" Then
   rs![LMStatus1] = Me.[Loss Mit Status].Value
ElseIf Trim$(LMStatus2 & "") = "" Then
   rs![LMStatus2] = Me.[Loss Mit Status].Value
ElseIf Trim$(LMStatus3 & "") = "" Then
   rs![LMStatus3] = Me.[Loss Mit Status].Value
ElseIf Trim$(LMStatus4 & "") = "" Then
   rs![LMStatus4] = Me.[Loss Mit Status].Value
[blue]etc.[/blue]

 

[boredguy2000]

It WAS that simple! You guys have really been great! Thanks again! (I seem to love !'s more than I thought)