Update Record with 2nd Sub Form 1

[stlrain95]

I need to update a Master table with a 2nd subform that is filled out later.

But obviously it has to search for the existing data first and then insert the updated data.

Any suggestions??

 

[Nowell]

Hi,

Not too sure what this means? can you be a bit specific

 

[stlrain95]

I have a form that I fill in some initial data into a MASTER table.

At a later time, I need to update some date information in this table. In order to do this, I have to know some of the original data like Lot Number and Part Number. It is date/time stamped.

So the second subform. I will scan in the lot # and then have the form look up the lot # and assign the new data.

But I haven't been able to write the right code.

Any suggestions?

 

[Nowell]

ok I think I can do this, you need to assign the lot# to a variable, and then run a recordset to look for that lot# in the master table, then if it is found you need to do an update recordset on the master table using the dates as vb variables too.

 

[stlrain95]

Okay....

I have start form that feeds into a Master Table.
With the Lot Number
Date/Time Stamp

I then have a subform(2nd Form)
With a Lot Number Field
And Date/Time Stamp.

I need to push this Date/Time stamp from the second form into the original table based on Lot Number.

Does that make better sense??

 

[stlrain95]

For some reason didn't update....

Do you have some code examples for this??

 

[Nowell]

Yep no worries mate, just finished work but I will get on to it at home, supply me with what you have already written that way I don't have to learn the names of your text fields.
I hope you going to give me a star for this I might miss friends.

 

[stlrain95]

Okay

I don't have a lot of code....just a mess

This adds record to Master Table
Field Names
TxtLot = Master.Lot_Number
TxtPart = Master.Part_Number
TxtStage (stage Date/Time)= Master.Stage
TxtInspector = Master.Inspector

2nd form
TxtLot = Unbound
TxtPart = Unbound
TxtStart = Unbound
TxtInspector = Unbound

I just need to add the Start Date Time to the existing data in the Master Table.

 

[Nowell]

Ok you want to do something like this.

In the after update event of the TxtStart in your second unbound form do this:

Dim start, lot, strSQL as string
Dim rst as ADODB.Recordset

start = TxtStart
lot = TxtLot

strSQL = "SELECT * FROM Master
WHERE Master.Lot_Number = '" & lot & "'"
set rst = New ADODB.recordset
rst.Open strSQL, CurrentProject.Connection, adOpenkeyset, adLockOptimistic
if (Not rst.EOF) then
'run update query here
end if
rst.close

 

[stlrain95]

Thank you for the help!!

I appreciate it.

 

[Nowell]

No worries, hope it all made sense, where is my star god damn you (lol)

 

[stlrain95]

Got something for ya...

WHERE Master.LotNumber = '" & lot & "'" This is coming up with a syntax error.

Also, What do I need in the form of the Update Query??

Thanks,

 

[Nowell]

ok, what is the data type of the Lotnumber if it is numeric then replace '"& lot &"' with " & lot.

Where you do the update, you need another recordset like the one I created called rst2 and set the strSQL to something like

"UPDATE Master SET StartDate = '" & start & "'"

That should do ya

 

[stlrain95]

Here is what I put in...this is on the Click()

Dim start, lot, strSQL As String
Dim rst As ADODB.Recordset
Dim rst2 As ADODB.Recordset

start = TxtStart
lot = TxtLot

strSQL = "SELECT * FROM Master WHERE Master.LotNumber = " & lot & ""
Set rst = New ADODB.Recordset
rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If (Not rst.EOF) Then
strSQL = "UPDATE Master SET StartDate = '" & start & "'"
Set rst2 = New ADODB.Recordset
rst2.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
End If
rst.Close

It crashed on the rst2.Open....line.

What do you think?

 

[stlrain95]

Also, this will place the start time from the 2nd form into the original Master Table with that lot number?

Just wanted to make sure.

 

[Nowell]

hi

Change this: WHERE Master.LotNumber = " & lot & ""

to: WHERE Master.LotNumber = " & lot"

Not sure why rst2 should fail, what error did you get?

try using this instead of a recordset

If (Not rst.EOF) Then
strSQL = "UPDATE Master SET StartDate = '" & start & "'"
Docmd.RunSQL strSQL
End If

 

[stlrain95]

Okay...IT worked.

One last question.....

Can I make the Update Window go away??

the "You are about to UPDATE 1 line?" Yes/No

 

[stlrain95]

Oops....Let me get all of this.

Dim start, lot, strSQL As String
Dim rst As ADODB.Recordset
Dim rst2 As ADODB.Recordset

start = TxtStart
lot = TxtLot
Part = TxtPart

strSQL = "SELECT * FROM Master WHERE Master.LotNumber = " & lot & " and Master.PartNumber = '" & Part & "'"
Set rst = New ADODB.Recordset
rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If (Not rst.EOF) Then
strSQL = "UPDATE Master SET Start = '" & start & "'"
DoCmd.RunSQL strSQL
End If
rst.Close


I had to add another criteria to the select statement.

BUT, it updated all my fields in the table with the START time, not just the field based on the query.

 

[stlrain95]

Nowell, haven't heard from you. Any suggestions on this?

 

[Nowell]

Hi,

Sorry been off sick for a bit,

try this

strSQL = "UPDATE Master SET Start = '" & start & "'" WHERE Lot_Number = '" & lot & "'

This reason for using the Not rst.EOF bit is for error trapping data input, it will only update the table if a record with that criteria exists