Input a value into a field using VB6

[PALman]

Can anyone help me code the following. I have picked up help on various bits of this in other forums but would like to see an example of coding from start through to finish so I am posting on this forum which may be more appropriate since it deals with databases...
From a Control Button on a VB6 form I want to...
1) Connect to an Access 2000 Database named "QC6.mdb"
2) Open Table named "JobDetails"
3) Search for a record in field named "JobNo" for a job number variable (ex. 245001)
4) Go to another field named “DocumentPresent” in the same record which is of Yes/No field type and have the record marked as “TRUE”
5) Save and close the Table
6) Close the database “QC6.mdb”
When I refresh/return to the VB6 Form I then would see that the CheckBox bound to the field “DocumentPresent” would then have a tick in it.
Any help is much appreciated
Thanks

 

[ChrisHirst]

At the simplest use;

Dim conn As ADODB.Connection
Dim strSQL As String
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=/pathtodb/qc6.mdb"

strSQL = "UPDATE JobDetails SET DocumentPresent = true WHERE JobNo = 123456 ;"
conn.Execute strSQL
conn.Close
Set conn = Nothing

in the cmd_Click() sub



Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[PALman]

Thanks Chris,
Your code was just what I needed.
I did have to change it a little as I was getting a "type mismatch" error.
So here is the final coding...
Private Sub Command4_Click()
Dim Conn As ADODB.Connection
Dim strsql As String
Set Conn = New ADODB.Connection
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\000-QualityControlProgram\QC5.mdb"

strsql = "UPDATE QPStatusNEW SET QPStatus.DocPresent = True WHERE QPStatus.JobNo='" & 221207 & "'"

Conn.Execute strsql
Conn.Close
datPrimaryRS.Refresh
Set Conn = Nothing
End Sub

I can now use this coding extensively throughout my project.
Thanks again

 

[HarleyQuinn]

Just for future reference, if you're hardcoding a value into the query you don't need the concatenation (as opposed to using a variable where it is necessary), you could just use:

strsql = "UPDATE QPStatusNEW SET QPStatus.DocPresent = True WHERE QPStatus.JobNo = '221207'"

Using a varuiable would be like:

strsql = "UPDATE QPStatusNEW SET QPStatus.DocPresent = True WHERE QPStatus.JobNo='" & yourVariable & "'"

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PALman]

Thanks HarleyQuinn
I shall be using variables throughout my project. So what you posted...
strsql = "UPDATE QPStatusNEW SET QPStatus.DocPresent = True WHERE QPStatus.JobNo='" & yourVariable & "'"
is exactly what I need. I used the hard coding in my initial post just to get started and now I have taken it on from there.
Thanks again, to you and to Chris, for initial response.