refresh/go

[corda911]

i'm setting up a database that will be used in stock taking for a retail store.
finding an item is a matter of choosong/typing in a supplier code, then a stock code, and if in the case of cosmetics theres different colours, then an item code.
But i need the program to refresh itself.
there is a frame on the right of the "code" text boxs with option buttons inside of it, each option button represents a different aspect of information that can be found out about that project eg. in stock, price, cost, description etc.
when you have typed in the 3 codes and selected one of the option buttons the information you have chosen will appear in a text box to the right of the frame i figure the stongest and fastest way of having this occur would be to use a refresh/go style button but am unsure how to go ahead and don't know of any alternatives just yet currently as you choose each code the information is displayed immediatly but that is due to the use of a drop box which will be going shortly and in its place shall be a list box
i have not yet added coding for the frame or the option buttons it contains currently the whole program strongly resembles the result of the database tutorial by one of the members of VB forums(he has a link for it any way he does not claim to be its creator) anyway help would be appreciated

specs: Microsoft Access 2000
VB6
ADODB connection
provider unknown

[eSPiYa]

Pls post the screenshot of the Form.
I'm not really good in reading w/o illustrations or images.

Then I'll try to help you.

[corda911]

image is in the attachment form is how it looks now fin form is similar to how i want it to look

[eSPiYa]

Do the Go/Find button have code already?

I'm sorry but I'm not really good in english.

[corda911]

not currently im not exactly sure in how to go ahead with the programming for that particular button

[eSPiYa]

I'm not sure but try this:

Create a sub that will:
1) Call the data from your database, filter it w/ the value of your textboxes and dropdownbox. Use a query, ex. select * from table where StockCode=stockcode and ItemCode=itemcode and SupplierCode=suppliercode
2) Display the filtered data to the Textbox.

Call this Sub under the Go/Find button's Click event.
(you may call this sub on the Click event of the RadioButtons)

[corda911]

im a little new to databasing how do i call up the data from the database
i understand the filter part put simply its just checking the data for matches to the text and drop boxes and not saving things that dont match and whats a query

BTW: im only in yr 12 i've only recently started work in VB i only have a minimal knowledge of vb coding

[eSPiYa]

Ok!
So how much you know about VB (variables, objects, events, database, ado, dao, etc.)?

[corda911]

ummm variables, objects i might and a bit of database
thats really about all

[eSPiYa]

How about Events, Subs, Functions, ADO/DAO?

You will need to know how to use these in your project.

[corda911]

subs and functions i know some about events i've never heard of the name and ado/dao rings a bell

[eSPiYa]

Let's start our lesson in Database:

ADO/DAO are objects used in connecting to a database like MSAccess, Spreadsheet, Textfiles, etc. (SQL Server is applicable only in ADO and not in DAO)

DAO (Data Access Object) - is easy way to use (Datacontrol is easy to use too but it I prefer to use DAO)
ADO (ActiveX Data Object) - It is an advance database access object (because it is advance it is not easy to use for those who are new in database development), you may use it to connect to database servers like SQL Server, MySQL, etc.

W/c do you want to use?
I suggest me must start in DAO.
You said you are using MSAccess as your database, right?!

[corda911]

yeah and im pretty sure that program is set up in ado i had to change settings to include active x data objects in one of the drop menus

[eSPiYa]

this is how to add a reference to ADO:

go to Project>>References then check Microsoft ActiveX Data Objects version Library (choose the highest version that you have, I have version 2.8)

Then you must know how to create a connection string

[si_the_geek]

i have not yet added coding for the frame or the option buttons it contains currently the whole program strongly resembles the result of the database tutorial by one of the members of VB forums(he has a link for it any way he does not claim to be its creator)

That sounds familiar!

I'm guessing you are refering to the ADO Tutorial link in my signature, and so have the three "code" boxes fully functional already.


In terms of using a "go/find" button, that is probably the best method, but it can be easily changed later anyway (you would just need to move the code).

In order to make it work you will need another recordset object, and fill this using an SQL statement. For an explanation of how to do that, see this new 'extension' to the tutorial.

Your code should look something like this:

Dim rsFind As ADODB.Recordset 
Dim strSQL As String
 
'** set up strSQL here
 
  rsFind.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
 
'** fill the controls here
 
  rsFind.Close
  Set rsFind = Nothing

[corda911]

how i understand it is that my code for the search button should be as follows

Private Sub cmdSearch_Click()
Dim rsFind As ADODB.Recordset
Dim strSQL As String
 
'** set up strSQL here
strSQL = "SELECT * FROM tb1_master"
If Combo2.Text <> "" Then
 strSQL = strSQL & " WHERE stock code  = " & Val(Combo2.Text)
End If
  rsFind.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
 
'** fill the controls here
 
  rsFind.Close
  Set rsFind = Nothing

but will this work how i want for example
my database comprises 4 columns (not really but for examplery purposes)
first column is supplier code
second column stock code
third column item code
and fourth column description
there are thousands of items each one has the exact same supplier code (eg 160)
so if i search the supplier code column for 160 i will get 1000 matches but it will only say 160 over and over again right?
i want it to show me the stock codes that are stored within that supplier(160) in the (newly created/adjusted) combo box(instead of text box this way it will show me all stock codes if i click the drop button that are in that supplier) i can then choose a stock code(eg 45,46 or 47) each of these stock codes contain 10 items each of those items have and item code (individual products of an item type) for our purposes those codes are 1 - 10 (ie full code on a product on shelves reads 160,45,1 or 160,47,1)i choose a item number and click our search button (for the third time one click for each search i have searched the supplier codes, the stock codes and now the item codes)the text box on the right of the screen should show me a description of that individual item is that what this coding should help do

[si_the_geek]

I didn't make it clear enough in the other thread about searching multiple columns, so have now extended it to explain how to do that.

Please note that having spaces in your field names will cause you problems, as the database thinks the field name is just stock, and will wonder why you have written code after it (and give you an error). A temporary fix is to surround the name with square brackets ([stock code]), but this will not correct it in all situations.


In order to get "160" to only show once, the simplest way is to tell the database to only give you unique data, which can be done with the Distinct keyword, eg:

SELECT DISTINCT SupplierCode FROM tb1_master

(if you are doing this, you should be using a separate recordset to the "main" one)


I'm afraid I didn't understand your post entirely, but this should help you correct most of the issues.

[corda911]

ill explain better (it was actually a question rather than on whether it would work in the way i specified) you have a database comprising of columns and rows the idea of the search button is that it will scan a chosen column for a match to what the user enters lets say that the search was based in column 2 to and the match is found at row 4 all the information in row 4 is then to be displayed but i have chosen (through the use of option buttons) to only see information located in column 5. so the information that should be displayed is located in column 5, row 4 this information will be displayed in the text box situated to the right of the form is that better i think i've worded it better ill add an attachment to this message showing the form and coding

[si_the_geek]

First off, when uploading code please post the actual code file (which is .frm for Forms), otherwise we have to create one and put the code in just so we can read it (which is not as good, as we dont have the controls etc on it).

Next up, there is a problem with your "fill" sub - it should have a check for EOF/BOF like FillFields had (otherwise you will get errors when no data is found, etc).

I also noticed that in Form_load you are only filling one combo, whereas you should be filling all three.

As you are not using any textboxes etc, and do not have any Next/Previous buttons, there is no need for the Fill sub at all - as the combos should be simply be filled in Form_load, and no other data is needed.


As for getting the right row of data - that is done by the SQL statement. Only data that matches your criteria (in the currect state, this is "stock code" [which you must correct, as mentioned above]) will be returned from the database.

To get the right column, just specify it as has been done for the others, eg:

Text1.Text = rsFind.Fields("Suppliers code") & ""

[corda911]

is there a way to save the number of a row and use that number somewhere else if so i thought (since im still having troubles with displaying the correct information) i can save the row number that corresponds with the match in the search and then make the text box's text equal ,say, column 5 row(saved row numer here) so is there a way to save a row number then call upon it again. there is an error in the find button that has to do with opening the connection for th rsfind ....dadada im not sure the exact wording

[si_the_geek]

Where databases are concerned, Row numbers dont exist... a database contains a "set" of data, there is no ordering involved at all (it is possible that if you return the same data from the database twice, it will be in a different order each time).

You should also not be thinking in terms of columns (usually called fields) having numbers either, as this varies depending on what data you are returning, and how you are returning it. Also, if the database ever changes (eg: fields added/removed) the numbers will no longer be correct. You should use the field names instead (as shown all thru the code), which will not have any issues.


What you need to do is use SQL statements (like that created for rsFind) to get the row of data that you want, and use the field name to return the correct field and put it into the controls (as shown at the end of my previous post).

As to the error, it should not be anything to do with the connection (as it worked fine in form_load), but after a quick look I can see that I missed an important line, which needs to be placed before the rsFind.Open line:

Set rsFind = New ADODB.Recordset