Populating an Update Qry via Form

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Populating an Update Qry via Form

Author

Topic

Bishop
Starting Member

5 Posts

Posted - 2004-11-30 : 12:27:41

I'm trying to populate an update query via a form, but I continue to get an error stating, "User-define type not defined." Here is the code

'************************************************************
' Update Query - A1c data
'************************************************************
Private Sub updt_A1c_rec_query()

Dim dbs As Database 'This is where the debugger flags me
Dim qdf As QueryDef
Dim a1c, Mbr, strSqlu As String

'A1c values from the form
Mbr = Me![CmboMemName].Column(0)
a1c = Nz(Me!txtA1c_1.Value)

'Return reference to current database.
Set dbs = CurrentDb

'Refresh QueryDefs collection.
dbs.QueryDefs.Refresh

' For Each qdf In dbs.QueryDefs
If qdf.Name = "updtqryA1c" Then 'This is the name of the update query, do I need to
dbs.QueryDefs.Delete qdf.Name
End If
Next qdf

' Create SQL string to select the member record to update.
strSqlu = "Update [tbl_main] As t1 SET "
strSqlu = strSqlu & "t1.[a1c]= '" & a1c & "'"
strSqlu = strSqlu & " WHERE t1.[MEMBER_]= '" & Mbr & "';"
qdf.Execute
Set dbs = Nothing
End Sub

Will someone help me with the following.
1) I have no idea whats wrong with the Dim statement
2) Do I need to build the update query in advance, and if so I'm confused on how to work in the As t1.** logic.

Thanks a ton.

Bishop

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-30 : 13:07:30

you need to make sure you have a reference set to the DAO object library. and always prefix objects from that libary with "DAO", since both ADO and DAo have many objects named exactly the same (i.e., "recordset" and "field").

when you code is visible on the screen, go to Tools -- > References

- Jeff

Bishop
Starting Member

5 Posts

Posted - 2004-11-30 : 14:14:23

Jeff,

Thanks a bunch! I did what you said and now I'm off and running to the next problem :D

Take care,

Sean

Here is what I have so far...if anyone can ever get some benefit out of it, please feel free to do so. What this code does is creates a combo box from an existing query and allows a user to update an existing table via a free floating form.

Private Sub CmboMemName_AfterUpdate()
Dim memname As String
memname = DLookup("[memname]", "tblMembInfo", "[member_]='" & Me!CmboMemName.Column(0) & "'")
Provname = DLookup("[PCP]", "tblMembInfo", "[member_]='" & Me!CmboMemName.Column(0) & "'")
DOB = DLookup("[ymdbirth]", "tblMembInfo", "[member_]='" & Me!CmboMemName.Column(0) & "'")
Me!lblMemName.Caption = memname
Me!lblProvName.Caption = Provname
Me!lblMemDOB.Caption = DOB
End Sub

Private Sub btnUpdtA1c_Click()
Dim Msg, Style, Title, Response, Response2 As String
Dim Mbr As String
Dim Cur_Lock As String
Dim ReqSat As Boolean

Mbr = Me![CmboMemName].Column(0)
Cur_Lock = Nz(DLookup("[a1c_Lck_Dat]", "tbl_main", "[MEMBER_]='" & Mbr & "'"))
ReqSat = Nz(DLookup("[a1c_Req_Satsf]", "tbl_main", "[MEMBER_]='" & Mbr & "'"))

If (ReqSat = True Or Not (Trim(Cur_Lock) = "")) Then
Msg = "This section of the record is locked. Please contact the manager to unlock the record or edit different record." ' Define message.
Style = vbOK
Title = "This record is locked, changes will not be saved." ' Define title.
Response = MsgBox(Msg, Style, Title)
Else
Msg = "Are you sure you want to update the A1c record ?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "MsgBox Last Chance to Abort the Changes" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
updt_A1c_rec_query ' Update the record
Else ' User chose No
Style = vbOK
Msg = "Please return to the record for further editing or select the new member. All " _
& "changes will be discarded"
Response2 = MsgBox(Msg, Style, Title)
End If
End If
End Sub

'************************************************************
' Update Query - A1c data
'************************************************************
Private Sub updt_A1c_rec_query()

Dim dbs As Database
Dim qdf As QueryDef
Dim a1c, Mbr, strSqlu As String

'A1c values from the form
Mbr = Me![CmboMemName].Column(0)
a1c = Nz(Me!txtA1c_1.Value)

'Return reference to current database.
Set dbs = CurrentDb

'Refresh QueryDefs collection.
dbs.QueryDefs.Refresh

For Each qdf In dbs.QueryDefs
If qdf.Name = "updtqryA1c" Then
dbs.QueryDefs.Delete qdf.Name
End If
Next qdf

' Create SQL string to select the member record to update.
strSqlu = "Update [tbl_main] As t1 SET "
strSqlu = strSqlu & "t1.[a1c]= '" & a1c & "'"
strSqlu = strSqlu & " WHERE t1.[MEMBER_]= '" & Mbr & "';"
Set qdf = dbs.CreateQueryDef("updtqryA1c", strSqlu)
qdf.Execute
Set dbs = Nothing
End Sub

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources