Reducing SQL code 2

[tag141]

Sorry, me again. Another one of my projects. This time for organising and controling stock. It uses a VB6 front form and access as the database. There are more than 50 items in the db and I would like to reduce the amount of code in the app.

If the user enters 'stockitem1' into the textbox as a barcode, it runs an SQL.

If the user enters 'stockitem2' into the textbox as a barcode, it runs an SQL etc.

Each SQL performs exactly the same function. I was hoping to just have one line of SQL code and somehow have the app recognise the barcode number in the textbox.

If txtSearch = "123456" Then
Set rs = conn.Execute("UPDATE tblStock SET kitdate = #" & Format(Now(), "dd/mmm/yy") & "#, quantity = quantity + 5 WHERE [reagent] = '" & txtSearch.Text & "'")

So instead of having 50 lines and SQLs each with a different txtSearch number I could have something in place of the 123456 so it knows which item it is.

Sorry if it's difficult to understand but it's difficult to explain.

TIA

 

[HarleyQuinn]

Sorry to ask but could you show us a couple of the statements you have in your code as the one you've shown should pick up the value of txtSearch no matter what it is if you see what I mean. I'm not sure why you need the IF statement?

Cheers

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

 

[tag141]

My code at the mo loks something like this

If txtSearch = "123456" Then
Set rs = conn.Execute("UPDATE tblStock SET kitdate = #" & Format(Now(), "dd/mmm/yy") & "#, quantity = quantity + 5 WHERE [reagent] = '" & txtSearch.Text & "'")
else
If txtSearch = "123457" Then
Set rs = conn.Execute("UPDATE tblStock SET kitdate = #" & Format(Now(), "dd/mmm/yy") & "#, quantity = quantity + 5 WHERE [reagent] = '" & txtSearch.Text & "'")
else
If txtSearch = "123458" Then
Set rs = conn.Execute("UPDATE tblStock SET kitdate = #" & Format(Now(), "dd/mmm/yy") & "#, quantity = quantity + 5 WHERE [reagent] = '" & txtSearch.Text & "'")
etc

Where 123456 is a stock code.

There would have to be 50+ lines of SQL code for the 50 different stock items. The code works perfectly if I precode individual stock codes. I wanted to just have one SQL. I know it will annoy the crap out of people trying to help but I do have trouble explaining my projects

 

[HarleyQuinn]

This will really get on your nerves!

All of your SQL does EXACTLY the same thing, you don't need the IF statement in at all.

This is because the SQL is dynamically picking up the vlaue from txtSearch.

Try cutting out the IF statement completely and just using:

Set rs = conn.Execute("UPDATE tblStock SET kitdate = #" & Format(Now(), "dd/mmm/yy") & "#, quantity = quantity + 5 WHERE [reagent] = '" & txtSearch.Text & "'")

That should cut down you code massively.

I might not have explained why you don't need the IF's very well but if you need any clarfication please post back and I will happily try and explain myself better!

Hope this helps

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

 

[Golom]

HarleyQuinn is right of course but I'm wondering why you would have done this at all. It's usually a very bad idea to hard-code data values. You really don't want to be modifying the code every time a new stock code is created.

Are there some values that may appear in txtSearch.Text where you don't want to run the SQL?

If so, how would you distinguish the ones where the SQL should run from those where it should not by some means other than requiring that the specific data values appear in code?

 

[HarleyQuinn]

It's usually a very bad idea to hard-code data values

I couldn't agree more.

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

 

[Andrzejek]

If you have a set list of stock codes, why don't you pull it from your database and populate a combo box for user to choose from? It would be a lot easier rather than having user enter stock code in a text box - which you then need to validate, right?

As for new stock codes - create a place where you can add a new one to your database.

HTH

---- Andy

 

[tag141]

This will really get on your nerves!

No things don't get on my nerves when I'm learning and learning from people who know what they are talking about.

I didn't want all the IF statements, that's why I asked the question if I could reduce them...and I can, and I have.

I do have 2 SQLs that run depending on the value in the text box. Thanks to your advice I have sorted some issues.

 

[tag141]

I have this now. Does it look better?

Private Sub cmbAdd_Click()

  Dim conn As ADODB.Connection
  Dim rs As ADODB.Recordset

  Set conn = New ADODB.Connection
  Set rs = New ADODB.Recordset

  conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = c:\stock\db1.mdb"
  conn.Open

Set rs = conn.Execute("UPDATE tblStock SET kitdate = #" & Format(Now(), "dd/mmm/yy") & "#, quantity = quantity + 5 WHERE [reagent] = '" & txtSearch.Text & "'")
lblreagent.Caption = " & reagname & kit loaded into stock"

Set rs = conn.Execute("UPDATE tblStock SET botdate = #" & Format(Now(), "dd/mmm/yy") & "#, quantity = quantity - 1 WHERE [reagent1] = '" & txtSearch.Text & "'")

End Sub

Is there any way to get that label caption to show reagent name [reagname] according to which SQL runs?

 

[JerryKlmns]

Private Sub cmbAdd_Click()

  Dim conn As ADODB.Connection

  Set conn = New ADODB.Connection
  With conn 
     .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\stock\db1.mdb"
     .Open
     .Execute("UPDATE tblStock SET kitdate = #" & Format(Now(), "dd/mmm/yy") & "#, quantity = quantity + 5 WHERE [reagent] = '" & txtSearch.Text & "'"), , 129
     lblreagent.Caption = txtSearch.Text &" kit loaded into stock"
     .Execute("UPDATE tblStock SET botdate = #" & Format(Now(), "dd/mmm/yy") & "#, quantity = quantity - 1 WHERE [reagent1] = '" & txtSearch.Text & "'"), , 129
     .Close
End With
Set conn = Nothing
End Sub

For action SQL statements, a recordset is needless because the provider returns an empty recordset! The ,,129 are options passed to the provider to interpret the SQL statement to be the sum of the values of
adCmdText (=1 --> Text) + adExecuteNoRecords (= 128 --> Do not return any records) = 129
and thus, enjoy faster execution.

 

[tag141]

Ah, yes the rs. That was probably a legacy of copying and pasting from a tutorial that worked for me. I don't quite understand the 129 thing but I'll run with.

Any ideas about including [reagname] in the label caption instead of the txtSearch value?

 

[HarleyQuinn]

I'm assuming that reagname is a field in the table you're updating?

If so you will need to return that value in a query and set the label.caption to the value returned in the query.

Hope this helps

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

 

[JerryKlmns]

Now I get it. You need the name of the agent and not the ID. Right?
lblreagent.Caption = DLOOKUP("reagname", "TheTableYourYouStoreThem", "reagent=" & txtSearch.Text)

 

[HarleyQuinn]

Jerry, would it not be easier to use a recordset (especially as the OP is using ADO) in VB6 than to reference an Access Object to use DLOOKUP?

Cheers

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

 

[JerryKlmns]

Ooops!

One bucket of strong black coffee, pls! And harry!

Oh!Hmmmm... Yes. Indeed. You 're right. Hmmmmm!

Dim rst = ADODB.Recordset
Set rst = Conn.Execute("SELECT reagname FROM TheTableWhereYouStoreThem WHERE reagent='" & txtSearch.Text & chr(39))
lblreagent.Caption = rst.Feilds(0)
rst.Close
Set rst = nothing

Sorry about that...

 

[HarleyQuinn]

No worries

At least we've got the OP an answer between us

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

 

[JerryKlmns]

 

[tag141]

I gave Jerry a big pink star as it is equal or greater to a bucket of black coffee. I would like to know one other thing from you guys.

This project had to be simple. It's used by many staff from different departments, at all hours of night and day (but no multiple connections), so too many mouse clicks or buttons to push they'll get brassed off and won't use it. It has to use a front end but not nessecarily(?) access as the db. All staff have to do is wand in a barcode and that's it. It has to add a quantity according to one set of unique codes and subtract a quantity according to another set of unique codes.

How would you have approached it? Obviously I don't want code but as I'm very wet behind the ears with programming I wondered how a competent programmer would have made the app work.

 

[johnwm]

1. Get the input from the wand into the textbox
2. Use the textbox change event to check the length of the string
3. When string is long enough (barcode + terminator) run the query and update the label

Nothing else on the interface. That way when they scan it, it all just happens.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[tag141]

Johnwm, that's exactly what I want tem to do and what I had in Access. However, all staff aren't allowed access to access so can't run the db. That is why I'm creating it in VB. What I was really after was how would you distinguish between the two unique codes and two SQLs.

Incidentally...how do you do 2 and 3 from your list.?

Thanks for your input.