Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
boredguy2000

boredguy2000

Programmer
Jun 29, 2007
30
US
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!!!
 
Sort by date Sort by votes
You have to instantiate a table object before you can use it. Don't name it "table", as that is a reserved word.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."
 
Upvote 0 Downvote
The "Table![workingtable].[LMStatus1]" is referring to the (possibly)filled table field...

So I need to somehow initiate the table before my IF test? I'm not sure that I know what you mean, or what to do at this point really.
 
Upvote 0 Downvote
I may understand a bit more (emphasis on may). I have this now:


Private Sub Capture_Status_Change_Click()

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef


Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.Table("WorkingTable")


If WorkingTable![LMStatus1] Is Null Then


Let tdfCurr.[LMStatus1] = Me.[Loss Mit Status]
Let tdfCurr.[ApprovalDate1] = Me.[ApprovalDate1]

Else

Let tdfCurr.[LMStatus2] = Me.[Loss Mit Status]
Let tdfCurr.[ApprvalDate2] = Me.[Loss Mit Status]

End If


End Sub

Again thank you everyone/anyone for your help!
 
Upvote 0 Downvote
You need to understand the difference between a Table and the contents of a Table.

A TableDef gives you information about the metadata for the table. For example, the fields in the table, the data types of those fields, the indexes that are defined, etc.

That is distinct from the data stored in the table. You retrieve that into a Recordset object.

Your code looks like you are trying to use a TableDef to modify the data in the table ... not the attributes of the table ... and that won't work.

Here's an example of how to deal with the data in a table
Code: 
Private Sub Capture_Status_Change_Click()

   Dim dbCurr As DAO.Database
   Dim rs     As DAO.Recordset

   Set dbCurr = CurrentDb()
   Set rs = dbCurr.Openrecordset("WorkingTable")

   If NOT rs.EOF Then   ' Make sure that some records were returned.
      rs.Edit           ' Start changes to the record

      If IsNull(rs![LMStatus1]) Then
         ' Modify values when NULL
         rs![LMStatus1] = Me.[Loss Mit Status]
         rs![ApprovalDate1] = Me.[ApprovalDate1]
  
      Else
         ' Modify values when NOT NULL
         rs![LMStatus2] = Me.[Loss Mit Status]
         rs![ApprvalDate2] = Me.[ApprovalDate2]

      End If
   
      rs.Update  ' Write the modified record to the database

   End If

End Sub

Note that your recordset may contain many records. The above code will modify only the first one.
 
Upvote 0 Downvote
Thank you very much Golom! I am going to go play around with this but am certain that it is just what I was looking for.

(Especially thank you for taking the time to actually associate my needs with the provided code. All the stars that i can give!)
 
Upvote 0 Downvote
After just a tiny bit of tweaking I have this code working pretty much as expected but one point that Golom pointed out and I did not really realize, is that It is only writing and subsequently OVERwriting the table field "LMStatus2" of the first record within the form - even when i cycle to the next record and use the button click.

My question: does anyone know how to modify this in order to have it write to the specific record that the user is on when pushing the button?

Thank you all for this help as well as all that i have ever received!
 
Upvote 0 Downvote
Why not simply use an UPDATE query with a proper WHERE clause ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
I am pretty much self taught and have pretty good success with most aspects but have not really gotten too in-depth with the SQL clauses.

I assume that my goal makes sense to you (a table with columns for multiple instances for saves (the status changes every couple of days and i need to capture these changes/dates in the subsequent columns of the table) coming from one field within the bound form).

A little direction with that idea would be extremely appreciated.

Alternatively the code idea is seemingly REALLY close as is...

As always thank you all!
 
Upvote 0 Downvote
Oh sorry, also I inserted this:
msgbox Me.CurrentRecord which does show me which record i am in (though it still writes to the first!), so maybe something with this "CurrentRecord" would be going in the right direction?
 
Upvote 0 Downvote
What is the PrimaryKey (name and data type) of workingtable and with which control is it related ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
I dont't have a PrimaryKey defined. The form is simply bound to the table (workingtable). The fields in question are:
Table: LMStatus1,LMStatus2; ApprovalDate1,ApprovalDate2

Form: Loss Mit Status, Approval Date

So the idea is each click will capture each subsequent "status" change and date.
I realize I am sort of repeating myself but since i couldn't exactly answer you PHV I figured I could at least try and clarify my intentions. :)
 
Upvote 0 Downvote
The form is simply bound to the table (workingtable)
So simply update the bound controls ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Well there are just the two fields: Loss Mit Status and Approval Date on the Form, while there are mulitple columns on the table (the fields LMStatus1, LMStatus2; ApprovalDate1, ApprovalDate2) that I need to write to..?

I'm not sure that I understand your last piece of advice, but thank you for working with me!)
 
Upvote 0 Downvote
I will gladly go in another direction if necessary as I am having obvious dificulties with the current approach, such as a "Write Conflict" message that I am getting in my, not even fully working approach!
 
Upvote 0 Downvote
FYI: this is the code thus far.

Code: 
Private Sub Capture_Status_Change_Click()

   Dim dbCurr As DAO.Database
   Dim rs     As DAO.Recordset

   Set dbCurr = CurrentDb()
   Set rs = dbCurr.OpenRecordset("WorkingTable")


    If Not rs.EOF Then   ' Make sure that some records were returned.
      rs.Edit           ' Start changes to the record

      If IsNull(rs![LMStatus1]) Then
         ' Modify values when NULL
        rs![LMStatus1] = Me.[Loss Mit Status].Value
        rs![ApprovalDate1] = Me.[Approval Date].Value
        msgbox "Written"

        
      Else
         ' Modify values when NOT NULL
      rs![LMStatus2] = Me.[Loss Mit Status].Value
      rs![ApprovalDate2] = Me.[Approval Date].Value
      msgbox "Written2"

      End If
       
      rs.Update  ' Write the modified record to the database

  End If

End Sub
 
Upvote 0 Downvote
What about this ?
Code: 
Private Sub Capture_Status_Change_Click()
   Dim rs     As DAO.Recordset
   Set rs = Me.Recordset
   Me.Dirty = False
   rs.Edit
   If IsNull(rs![LMStatus1]) Then
      ' Modify values when NULL
      rs![LMStatus1] = Me.[Loss Mit Status].Value
      rs![ApprovalDate1] = Me.[Approval Date].Value
      msgbox "Written"
   Else
      ' Modify values when NOT NULL
      rs![LMStatus2] = Me.[Loss Mit Status].Value
      rs![ApprovalDate2] = Me.[Approval Date].Value
      msgbox "Written2"
   End If
   rs.Update  ' Write the modified record to the database
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
PHV - that is outstanding it is working (almost) perfectly!

Two things (I really hope last!):
1) I'm not sure if you saw my question concerning writing to the current record or not; now it is doing this but with one hiccup. On each record after the first it is writing the first "capture" to LMStatus2 instead of 1 and THEN 2 ?

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?

Your fix also (as I'm sure you intended) took care of that error that I mentioned!

I know I've said this ad nauseam but I truly appreciate your help and all other help/tips this great board has provided me!
 
Upvote 0 Downvote
Replace this:
If IsNull(rs![LMStatus1]) Then
with this:
If Trim(rs![LMStatus1] & "") = "" Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Victory is mine(well yours PHV)! I'm sure i'll be back in a few days as the process develops but thank you again.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Moss100
  • Locked
  • Question
Vba adding record to table when field is integer
Replies
8
Views
378
Moss100
Moss100
Moss100
  • Locked
  • Question
Transfer data from external table to identical local table - urgent please
2
Replies
27
Views
662
Andrzejek
Andrzejek
V
  • Locked
  • Helpful tip
Capture Shift Key if Pressed on A form
Replies
3
Views
377
strongm
strongm
Zyborg
  • Locked
  • Question
Using a variable as reference to a form
Replies
5
Views
362
Zyborg
Zyborg
Moss100
  • Locked
  • Question
Add multiple contacts to outlook - from access table
Replies
12
Views
420
Moss100
Moss100

Part and Inventory Search

Sponsor

Back
Top