| View previous topic :: View next topic |
| Author |
Message |
KaleKYlssen
Newbie
Joined: 31 Jan 2004
Posts: 19
|
 Posted: Feb 8th, 2004 08:06 AM Post subject: help with database update |
 |
|
so far i have this to update a existing record and it works,
| Code: |
Private Sub cboStaff_Click()
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\Elco.mdb"
sqlefficiency = "SELECT * FROM Efficiency WHERE StaffID = '" & cboStaff.Text & "' AND WeekStarting = #" & cboWeek.Text & "#"
rsEfficiency.Open sqlefficiency, ConnString, adOpenKeyset, adLockOptimistic, adCmdText
End Sub
Private Sub cmdExit_Click()
Unload Me
frmMenu.Show
End Sub
Private Sub cmdUpdate_Click()
Dim IntUn As Integer
Dim IntAs As Integer
rsStock.Open
With rsStock
IntUn = CInt(rsStock.Fields("Assembled Stock"))
IntAs = CInt(rsStock.Fields("Unassembled Stock"))
rsStock.Fields("Assembled Stock") = IntUn + txtAmountMade.Text
rsStock.Fields("Unassembled Stock") = IntAs - txtAmountMade.Text
rsStock.Update
End With
rsStock.Close
With rsEfficiency
IntTime = CInt(rsEfficiency.Fields("TimeTaken"))
IntMotor = CInt(rsEfficiency.Fields("MotorsMade"))
.Fields("TimeTaken") = IntTime + txtTimeTaken.Text
.Fields("MotorsMade") = IntMotor + Val(txtAmountMade.Text)
.Update
.Close
End With
txtTimeTaken.Text = ""
txtAmountMade.Text = ""
End Sub
|
what i want to be able to do is when i enter a compund key that doesn't exist in the database, a new record will be added. To do this will i need to use the NoMatch function of sql,
any help appreciated |
|
| Back to top |
|
Andir
Centurion
Joined: 21 Dec 2003
Posts: 184
Location: Chicago Area
|
| Posted: Feb 9th, 2004 12:20 AM Post subject: |
|
|
I'm not sure that it would work, since I have not tried it personally, but you could attempt to to a database Execute. You connecting to the database in a similar way to ADO's connection/recordset method. With ADO, I usually declare a Database connection:
Private DB_something as ADODB.Connection
Private RS_something as ADODB.Recordset
and I use that to open the database:
DB_something.Open <connectionstring>, {user}, {pass}
then pull the recordset from that connection:
RS_something.Open <SQL Query>, DB_something, {additional record type flags, etc.}
You can use the DB connection object to run execution statements. I think you can use this to run stored procedures and such. MSDN states:
"Executes the specified query, SQL statement, stored procedure, or provider-specific text."
The format is as follows:
connection.Execute CommandText, {RecordsAffected}, {Options}
This returns a recordset of data if there is a return. Technically you can run your SQL statements this way.
Does that help any?
_________________
If you happen to see little people sitting on your desk...don't tell anyone or they might think your crazy too. |
|
| Back to top |
|
KaleKYlssen
Newbie
Joined: 31 Jan 2004
Posts: 19
|
| Posted: Feb 9th, 2004 02:17 PM Post subject: |
|
|
i may be able to do it that way but i don't want to change to mush of the coding, does any know is its posible to link a sql statement with an If statement and do it that way.
Thanks for your suggestion Andir  |
|
| Back to top |
|
KaleKYlssen
Newbie
Joined: 31 Jan 2004
Posts: 19
|
| Posted: Feb 15th, 2004 09:41 AM Post subject: |
|
|
| i'm still having trouble with this, haven't been able to find a way to add a new record if the sql statement doesn't return a record can anyone help |
|
| Back to top |
|
Avis
Junior Poster
Joined: 07 Oct 2003
Posts: 510
Location: India
|
|
| Back to top |
|
KaleKYlssen
Newbie
Joined: 31 Jan 2004
Posts: 19
|
| Posted: Feb 15th, 2004 10:18 AM Post subject: |
|
|
| WOW your a genius ( compared to me anyway), thats solved my problem thank you, iv'e been stuck on that for a while =D |
|
| Back to top |
|
|
