Autopopulate to new line in table

[kdoran]

I have a couple of tables

tblFieldTag
FieldTagId
Input
Span
Out
Description
ProblemLoop (Yes/No)
SelectDeselect (Yes/No)

tblFieldTagNotes
FieldTagNotesID (number)
FieldTagID
FieldNotesDate
FieldNotesText

On a form I have a subform that runs a query of ProblemLoops and displays the FieldTagID and a SelectDeselect checkbox (along with other info).

If I have finished the work with the Problem Loops I will then check the SelectDeselect checkbox for each FieldTagID that was finished.

On the main form are two unbound fields, date and text. I will type in the Date in one and the other I will type in something like ‘work Completed’ or whatever was done.

I will then push a button and a query will run and look for the SelectDeselect Check and if it is checked it will autopopulate the next line for the FieldTagID in FieldNotesDate and FieldNotesText table.

It will Also remove the SelectDeselect Check and the ProblemLoop Check (this part I have fiqured out).

What I am having trouble doing is figuring out how to have the info add to the new line in the tblFieldTagNotes for both fields. All I can seem to do is write over what was already there.


Thanks in advance,

Kelly

 

[smuckers]

Good Evening kdoran:

I use the following process of adding info to a new line in a table. To summarize:

1. Declare and open an ADO recordset.
2. Must use the ".AddNew" and ".Update" statements if adding a new line to a table.
3. I assume you have an autonumber field, FieldTagNotesID, as the primary key.
4. Capture the new autonumber, FieldTagNotesID, by assigning it to the module level variable, mintNewID. This is not necessary, but very useful if you need the value for further coding.
5. Clean up and close out the rst.

6. Omitting the ".AddNew" statement and still using the ".Update statement will allow you to change existing fields to a specific record. You will have to change the SQL statement by adding a "WHERE" type statement to choose your record.

This method works for me. Hopefully it will work for you too. Do not hesitate to ask any further questions.

Thanks,

Smuckers

Dim mintNewID As Integer
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
    With rst
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic
        .CursorLocation = adUseServer
        .Open "SELECT * FROM tblFieldTagNotes"
    End With

   With rst
      .AddNew
        !FieldNotesDate = Me.txtUnboundDateField
        !FieldNotesText=txtUnboundTextField
        mintNewID = !FieldTagNotesID
      .Update
   End With
            
    rst.Close
    Set rst = Nothing
    Exit Function

May your hysterical randomness be cured!

 

[kdoran]

Smuckers,

Sorry it took so long to reply, I went on vacation.

Ok I put this in the code so when the button is pushed and not in a query.


Dim mintNewID As Integer
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.CursorLocation = adUseServer
.Open "SELECT * FROM tblFieldNotes"
End With

With rst
.AddNew

!FieldNotesDate = Me.DateField
!FieldNotesText = Me.TextField
mintNewID = !FieldNotesID
.Update
End With

rst.Close
Set rst = Nothing
' Exit Function

This is the error I receive

"Item cannot be found in the collection corresponding to the requested name or ordinal"

what am I doing wrong (also I mislabeled the table, it should be tblFieldNotes as well as the ID it should be FieldNotesID)

Not sure how to fix.

Thanks in advance,

Kelly

 

[kdoran]

I am not sure how to add this to this code but there is no reference to the FieldTagID, which is a required field for the tblFieldNotes and it is how the FieldNotes are pulled up in other reports.

Any ideas,

Kelly