VBA Assistance

[ozbuoy]

Just wondering if someone could please point out the error in my code. I'm looking to add details onto one table and edit a field on a second table after looking it up.

Many Thanks

 Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim Str As String
Str = "[Dispatch_Number]= " & Me.[Dispatch_Number]
Set db = CurrentDb
Set rs1 = db.OpenRecordset("Job_Data_Completed")
Set rs2 = db.OpenRecordset("Job_Data")

rs1.AddNew
rs1("VulID") = Me.[VulID]
rs1("Dispatch_Number") = Me.[Dispatch_Number]
rs1("Arrival_Time") = Me.[Arrival_Time]
rs1("Completion_Time") = Me.[Completion_Time]
rs1("Work_Completed") = Me.[Work_Completed]
rs1("Further_Work") = Me.[Further_Work]
rs1("Non_Completion") = Me.[Non_Completion]
rs1("Completion_Reasons") = Me.[Completion_Reasons]
rs1("Engineer_Outcome") = Me.[Engineer_Outcome]
rs1("Completion_Timestamp") = Format(Now(), "dd/mmm/yyyy hh:mm:ss")
rs1("Completion_Date") = Format(Now(), "dd/mmm/yyyy")
rs1("Completion_Time2") = Format(Now(), "hh:mm")
rs1("Completion_LanID") = fOSUserName()
rs1("Version") = "3.00"
rs1.Update
rs1.Close

rs2.FindFirst (Str)
rs2.Edit
rs2("Status") = "Dispatched"
rs2.Update
rs2.Close

MsgBox "Job Completed"

Forms.frm_planning_board_ward_search.Requery

 

[CJ_London]

what error do you get? you should have a message. And what line does it error on - will be highlighted.

there are a couple of things:

rs1("VulID") is the wrong syntax. It should be either

rs1.fields("VulID")

or

rs1!VulID

I also suspect your completion date/time - date/time fields are numeric - (a form of double), they just have special formats to look like a date - you are trying to update with a string.

all you need is
rs1!Completion_Timestamp =Now()

and don't see why you are trying to store it in its separate components as well, but if you have your reasons you would use

rs1!Completion_Date = Date()
rs1!Completion_Time2 =Time()

 

[Galaxiom]

rs1("VulID") is the wrong syntax.

No. That is a valid syntax too. There are many ways to refer to a field in a recordset.

 

[Galaxiom]

BTW Instead of opening rs2 on the entire table then finding the records it would be vastly more efficient to open it on a query with the condition.

Another issue is that you appear to be breaching normalization by storing the dispatched information in two tables leaving the database open to storing conflicting records. Normally the dispatched status should be determined by using a query against the primary information.

 

[CJ_London]

No. That is a valid syntax too.

I stand corrected - learn something every day

 

[ozbuoy]

Good morning Guys
Thank you both for your comments.

There isn't any errors being displayed when I run it. The completed information is writing to the table as expected. Its the updating of the status that isn't working.

The database is only being used on a nightly basis to record information and then at the end of the night we archive the information and start again fresh the following night.

I used to run two separate scripts that were triggered from two command buttons but I was looking to combine both scripts into one command button.

The reason I have split it into two tables is because at the start of the night we can have up to 40 people writing information onto the first table "Job_Data" but the second table only has around 4-5 people updating it. I was just minimising the risk of something going wrong by splitting the table into two.

It's also extremely unlikely that there would be storing conflicts on the database as the unique key is the vulID and this is generated from a different system.

Many thanks

Chris

 

[ozbuoy]

I also suspect your completion date/time - date/time fields are numeric - (a form of double), they just have special formats to look like a date - you are trying to update with a string.

all you need is
rs1!Completion_Timestamp =Now()

and don't see why you are trying to store it in its separate components as well, but if you have your reasons you would use

rs1!Completion_Date = Date()
rs1!Completion_Time2 =Time()

The reason I am using the "format(now(), dd/mmm/yyyy) etc is because it prevents the date from defaulting to the American style of MM/DD/YYYY.

I guess I also split it up to make it easier in the future if I need to extract different elements of the timestamp.

Cheers


Chris

 

[arnelgp]

you can change this:

Set rs2 = db.OpenRecordset("Job_Data")

to

Set rs2 = db.OpenRecordset("Select * From Job_Data When Dispatch_Number=" & Me.Dispatch_Number, dbOpenDynaset)

... and this portion:

rs2.FindFirst (Str)
rs2.Edit
rs2("Status") = "Dispatched"
rs2.Update
rs2.Close

To:

If rs2.bof and rs2.eof then
rs2.addnew
rs2("Dispatch_Number")=Me.Dispatch_Number
Else
rs2.MoveFirst
rs2.Edit
End If
rs2("Status") = "Dispatched"
rs2.Update
rs2.Close

 

[ozbuoy]

you can change this:

Set rs2 = db.OpenRecordset("Job_Data")

to

Set rs2 = db.OpenRecordset("Select * From Job_Data When Dispatch_Number=" & Me.Dispatch_Number, dbOpenDynaset)

... and this portion:

rs2.FindFirst (Str)
rs2.Edit
rs2("Status") = "Dispatched"
rs2.Update
rs2.Close

To:

If rs2.bof and rs2.eof then
rs2.addnew
rs2("Dispatch_Number")=Me.Dispatch_Number
Else
rs2.MoveFirst
rs2.Edit
End If
rs2("Status") = "Dispatched"
rs2.Update
rs2.Close

Thanks for your help, I seem to be getting a syntax error on this line.

Set rs2 = db.OpenRecordset("Select * From Job_Data When Dispatch_Number=" & Me.Dispatch_Number, dbOpenDynaset)

Run time error 3131
Syntax error in FROM clause

Cheers


Chris

 

[ozbuoy]

I'm a complete and utter twat. I just realised that there was nothing wrong with it other than I was trying to update "Dispatched" with "Dispatched" when it should have been "Dispatched" with "Completed".

Thanks to everyone who has helped me.

Cheers


Chris