Create a form for use as an update query?

T

tish

I've been searching the boards, but couldn't find an exact answer to my
question:

I've created an update query for a database containing Claim #s and purge
information. To prevent the users from getting confused (or messing something
up!) in the query, I'd like to make a simple interface/form for the user so
they can input what the updated info should be and the parameters, click a
button and voila.

Is this possible? If I haven't posted enough information here, let me know,
or if I you have some search terms so I can find a post that addresses this
already.

Thanks!!
 
R

Richnep

I've been searching the boards, but couldn't find an exact answer to my
question:

I've created an update query for a database containing Claim #s and purge
information. To prevent the users from getting confused (or messing something
up!) in the query, I'd like to make a simple interface/form for the user so
they can input what the updated info should be and the parameters, click a
button and voila.

Is this possible? If I haven't posted enough information here, let me know,
or if I you have some search terms so I can find a post that addresses this
already.

Thanks!!
Click to expand...

Try googleing for adding/updating records with a form that has unbound
fields. I know you can do it but you will also need another form/query/
VBA proccedure to add new records as you need an append query for
that.

Here is an example link:

http://64.233.167.104/search?q=cach...te+data+unbound+form&hl=en&ct=clnk&cd=4&gl=us
 
N

NKTower

Let's assume table named 'tblCLAIMS' has

ClaimNo text 10
Item_1 text 10
Item_2 currency
Item_3 text 20

Create form with UNBOUND controls txt_ClaimID, txt_Item_1, txt_Item_2,
txt_Item_3
Create command button named btn_POST
Assumes that you've done validation, know that the value in txt_ClaimID is
good, etc.
This is bare-bones...

----------

Private Sub btn_Post_Click()
Dim SQL As String
Dim CRLF As String

' I like to do this to make the SQL statement
' easy to read in debug window etc.
CRLF = Chr$(13) & Chr$(10)
SQL = "UPDATE [tblCLAIM] " & CRLF & _
"SET [Item_1] = """ & txt_Item_1 & """, " & CRLF & _
" [Item_2] = " & txt_Item_2 & ", " & CRLF & _
" [Item_3] = """ & txt_Item_3 & """ " & CRLF & _
"WHERE ( [ClaimNo] = """ & txt_ClaimNo & """ )"
DoCmd.SetWarnings False ' get rid of "you are about to update..."
On Error GoTo Problem
DoCmd.RunSQL SQL
MsgBox "Record Updated", vbOKOnly, "Confirmation"
Me.txt_ClaimNo = Null
Me.txt_Item_1 = Null
Me.txt_Item_2 = Null
Me.txt_Item_3 = Null
Done:
DoCmd.SetWarnings True
Exit Sub
Problem:
MsgBox Str(Err.Number) & ":" & Err.Description, vbOKOnly + vbCritical,
"Update Error"
Err.Clear
GoTo Done
End Sub

---------
Notes:
1 The doubled up quotes where you want one quote character in the
SQL statement. There are no quotes wrapping txt_Item_2 because it is
a numeric value.
2 If record doesn't exist, it still says "Record updated". As I said, this
is bare bones.
 
T

Tish

There seem to be two of us using the same display name. I just found your
posting under Tish. That's the display name I've been using for several
years. I'm not certain how to report this duplication to Microsoft, but this
message chain was not started by me. Tish N.

NKTower said:
Let's assume table named 'tblCLAIMS' has

ClaimNo text 10
Item_1 text 10
Item_2 currency
Item_3 text 20

Create form with UNBOUND controls txt_ClaimID, txt_Item_1, txt_Item_2,
txt_Item_3
Create command button named btn_POST
Assumes that you've done validation, know that the value in txt_ClaimID is
good, etc.
This is bare-bones...

----------

Private Sub btn_Post_Click()
Dim SQL As String
Dim CRLF As String

' I like to do this to make the SQL statement
' easy to read in debug window etc.
CRLF = Chr$(13) & Chr$(10)
SQL = "UPDATE [tblCLAIM] " & CRLF & _
"SET [Item_1] = """ & txt_Item_1 & """, " & CRLF & _
" [Item_2] = " & txt_Item_2 & ", " & CRLF & _
" [Item_3] = """ & txt_Item_3 & """ " & CRLF & _
"WHERE ( [ClaimNo] = """ & txt_ClaimNo & """ )"
DoCmd.SetWarnings False ' get rid of "you are about to update..."
On Error GoTo Problem
DoCmd.RunSQL SQL
MsgBox "Record Updated", vbOKOnly, "Confirmation"
Me.txt_ClaimNo = Null
Me.txt_Item_1 = Null
Me.txt_Item_2 = Null
Me.txt_Item_3 = Null
Done:
DoCmd.SetWarnings True
Exit Sub
Problem:
MsgBox Str(Err.Number) & ":" & Err.Description, vbOKOnly + vbCritical,
"Update Error"
Err.Clear
GoTo Done
End Sub

---------
Notes:
1 The doubled up quotes where you want one quote character in the
SQL statement. There are no quotes wrapping txt_Item_2 because it is
a numeric value.
2 If record doesn't exist, it still says "Record updated". As I said, this
is bare bones.



tish said:
I've been searching the boards, but couldn't find an exact answer to my
question:

I've created an update query for a database containing Claim #s and purge
information. To prevent the users from getting confused (or messing something
up!) in the query, I'd like to make a simple interface/form for the user so
they can input what the updated info should be and the parameters, click a
button and voila.

Is this possible? If I haven't posted enough information here, let me know,
or if I you have some search terms so I can find a post that addresses this
already.

Thanks!!
Click to expand...
Click to expand...
 
Top