I have a linked DB with view's and tables. Naturally I can't edit anything with in a view since i'm using inner and left joins but I have it set up that I can change the Tables and here's where the problem occurs. In general I'm creating a Help Desk system for my employer and I have to add new Faults that we come across daily. I am using pre-made database with only the tables and columns we use.
So logically I will have to be able to add new faults and have them displayed. To display my faults on my "MainPage"<-(Form) I use a view from my SSMS(SQL Server Managment Studio) dbo_VwMainFaults<-(View) and to add a new fault I insert it in to a linked Table dbo_Faults<-(Table).
My dbo_VwMainFaults uses dbo_faults and few other tables like user and Area
Now my problem is that after adding a new fault in to my Faults Table I can't see it in my MainPage, The new data is for sure being inserted in to my Faults Table but my View doesn't re-query/update.
I know that my Faults Table is updated because if I change the record source in my mainpage to that table the new records show as intended but they don't appear if Record Source is set to the view table. :banghead:
My VBA Code to add new Ticket:
This is how I am updating/altering my Faults Table.
also I had a issue with ODBC linking my access front end to my DB so I had that error displaying code in place to explain why its causing it but I didn't fix it it just fixed it self after leaving it for the night.
My FaultGUID is a GUID to generate a random ID< That GUID was placed there by me and replaced the original PK faultID which was a integer. I've only replaced it because it saves me worrying about a ticket being added by two people at the same time and having the same ID... but anyway I some how feel like my PK FaultGUID is not linking my Table[dbo_faults] properly with the View [dbo_VwMainFaults]
My SQL code to create the view I need:
I feel like this SQL statement is missing something that just doesn't update it with the new Faults table. How could I work around it or make it to work and display new tickets as I added them.
So logically I will have to be able to add new faults and have them displayed. To display my faults on my "MainPage"<-(Form) I use a view from my SSMS(SQL Server Managment Studio) dbo_VwMainFaults<-(View) and to add a new fault I insert it in to a linked Table dbo_Faults<-(Table).
My dbo_VwMainFaults uses dbo_faults and few other tables like user and Area
Now my problem is that after adding a new fault in to my Faults Table I can't see it in my MainPage, The new data is for sure being inserted in to my Faults Table but my View doesn't re-query/update.
I know that my Faults Table is updated because if I change the record source in my mainpage to that table the new records show as intended but they don't appear if Record Source is set to the view table. :banghead:
My VBA Code to add new Ticket:
Code:
Private Sub btnSaveFault_Click()
Dim rs As Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("dbo_Faults")
rs.AddNew
rs.Fields("FaultGUID") = Left(Me.nFaultid.Value, 38)
rs.Fields("hdid") = Me.txthdid.Value
rs.Fields("username") = Me.Username.Value
rs.Fields("phonenumber") = Me.phonenumber.Value
rs.Fields("Symptom") = Me.Symptom.Value
rs.Fields("symptom2") = Me.symptom2.Value
rs.Fields("category2") = Me.category2.Value
rs.Fields("category3") = Me.category3.Value
rs.Fields("takenby") = Me.takenby.Value
rs.Fields("MessSent") = Me.MessSent.Value
rs.Fields("RequestTypeNew") = Me.RequestTypeNew.Value
rs.Fields("urgency") = Me.urgency.Value
rs.Fields("causedby") = Me.causedby.Value
rs.Fields("Status") = Me.Status.Value
rs.Fields("datereported") = Me.datereported.Value
rs.Fields("dateoccured") = Me.dateoccured.Value
rs.Fields("seriousness") = Me.seriousness.Value
rs.Fields("Areaint") = Me.Areaint.Value
rs.Fields("sitenumber") = Me.sitenumber.Value
rs.Fields("estimate") = Me.estimate.Value
rs.Fields("datecreated") = Me.datecreated.Value
rs.Fields("datecleared") = Me.datecleared.Value
rs.Fields("cleartime") = Me.cleartime.Value
rs.Fields("fixbydate") = Me.fixbydate.Value
rs.Fields("clearance") = Me.clearance.Value
Dim DAOErr As DAO.Error
Debug.Print "Error raised: " & Err.Number
For Each DAOErr In DBEngine.Errors
Debug.Print DAOErr.Number, DAOErr.Description, DAOErr.Source
Next DAOErr
rs.Update
MsgBox "New Ticket was Added"
rs.Close
Set rs = Nothing
db.Close
'Forms("HomePage").Form.SetFocus ' , , "FaultGUID = " & Me.nFaultid & ""
DoCmd.Close , NewTicket, acSaveYes
DoCmd.OpenForm "HomePage", acNormal
'Set rs = Forms("HomePage").Form.Recordset
'rs.FindFirst "FaultGUID = '" & Me.nFaultid & "'"
End SubThis is how I am updating/altering my Faults Table.
also I had a issue with ODBC linking my access front end to my DB so I had that error displaying code in place to explain why its causing it but I didn't fix it it just fixed it self after leaving it for the night.
My FaultGUID is a GUID to generate a random ID< That GUID was placed there by me and replaced the original PK faultID which was a integer. I've only replaced it because it saves me worrying about a ticket being added by two people at the same time and having the same ID... but anyway I some how feel like my PK FaultGUID is not linking my Table[dbo_faults] properly with the View [dbo_VwMainFaults]
My SQL code to create the view I need:
Code:
Create VIEW [dbo].[VwMainFaults]
AS
Select
f.Symptom,
f.symptom2,
f.takenby,
s.sdesc,
s.Ssitenum,
f.username,
f.phonenumber,
f.sectio_,
f.sitenumber,
f.cleartime,
f.datecreated,
f.hdid,
f.estimate,
f.datereported,
f.causedby,
f.clearance,
f.Areaint,
f.category2,
f.category3,
f.datecleared,
f.Slaid,
f.Slareason,
f.Slastate,
f.MessSent,
f.MessSentLast,
f.urgency,
f.Requesttype,
f.RequestTypeNew,
f.Approvedby,
f.Status,
f.seriousness,
f.dateoccured,
ar.aareadesc,
f.FaultGUID
From dbo.Site S
Inner Join dbo.Faults f
ON f.sitenumber = s.Ssitenum
Inner Join dbo.Area Ar ON f.Areaint = ar.Aarea
GOI feel like this SQL statement is missing something that just doesn't update it with the new Faults table. How could I work around it or make it to work and display new tickets as I added them.
