Having difficulty with databae application

[Stuartp]

Hi all. I am trying to create a database app to monitor customers and stock levels, but I'm rather new to all of this so while I know what I want to do and the basics of how I ought probably to do it, I don't know exactly what I need to do to make certain things work.

I have created my tables and made the relationships I need etc. stock, products and customers. I therefore want to be able to enter into the database the customer, what products they have and how much of them they have in stock, adding new customer and product details in as needed where they don't already exist.

I have been using the data control to bind controls to the database, but this seems like a cumbersome way of working. I would therefore like some method where I can use a combo box of customers, say, and the user can start to type and use some auto-complete function and it then returns in a grid a list of all the products they have in stock together with quantities. When the customer name is selected in the combo i would like all the other related fields on the form e.g. address etc to auto update to reflect the current information.

I already know how to use SQL select statements to select the right information and insert that into another table to retrive fields, but I dont see how this will work for me - I need to be able to select the actual data e.g. customer name, product code and stock level and then edit this data directly.

I then need to be able to change the stock level for that product being displayed and have the record saved.

What do I need to know to go about this? Am I going about it the wrong way using VB, should i just make an Access application (not necesarily easier)? I'd rather use my own VB program if I could.

Your help is much appreciated.

Stuart

 

[MikeCox]

If you've got the Data1 bound to a database, it sounds like you've at least got some functionality. I may offend some old timers here (and for some good reasons), but I recommend the Adodc1 data control.

Most of your coding will revolve around the Recordset object. It's your primary means of manipulating and reading information.

Adodc1.Recordset.MoveFirst 'or MoveNext, MovePrevious, and MoveLast

Adodc1.Recordset.AbsolutePosition 'first record is 1, not 0

Adodc1.Recordset.AddNew 'without some coding, the Ado will give you no indication that a new record is waiting to be added at this point. You may notice bound text boxes all go blank, but that's pretty much it.

Adodc1.Recordset.Delete 'deletes current record (and any child records if appropriate relationships are set up to cascade)

Adodc1.Recordset.Fields("MyFieldName&quot.Value 'returns the actual data in that field for the current record. You can also use:
Adodc1.Recordset.Fields(36).Value

Adodc1.Recordset.Refresh 'recordsets are stored in memory. This re-reads information from the database using the method set up at design time (RecordSource and CommandType properties)

Adodc1.Recordset.Update 'sends data from recordset to the database to commit any changes made

'this is unorthodox, but this is how I open a recordset with an SQL query. (it's the only way I can ever get to work for some reason)
Const ConnectionString as String = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb"
Const strSQL as String = "SELECT * FROM tblPublishers WHERE City = 'AL'"
Adodc1.Recordset.Close
'open here:
Adodc1.Recordset.Open strSQL, ConnectionString

After using this method, you'll need to rebind any controls affected:

Dim txt as TextBox
For Each txt in txtFields
Set txt.DataSource = adodc1
Next

With this, you should be able to write code in the click event of cboCustomers to populate grdOrders:

CurrentCustomer = cboCustomers.Text
strSQL = "SELECT * FROM tblOrders WHERE CustName = '" & CurrentCustomer & "'"
Adodc1.Recordset.Close
Adodc1.Recordset.Open strSQL, ConnectionString
Set grdOrders.DataSource = Adodc1 'if this causes error, try using an Adodc2 just for the grid and say grdOrders.Rebind instead, but make sure to close and open the right recordset

You can set the RecordSource to an sql string at design time, just set the CommandType to adCommandText first, otherwise it should probably be adCommandTable (not Unknown)

I hope I got you started anyway, there's quite a bit more (especially error handling, use Select Case Err.Number in every error handler, you'll thank me later)

Good luck and try not to pull your hair out ;-)

-Mike
Difference between a madman and a genius:
A madman uses his genius destructively,
A genius uses his madness constructively.

 

[StaceyG]

StuartP,
I'm with you on the data control. It just seems to constricting to me, but other people love it.
Here is an example (below) of a form where the user can select a customer name or ID number from a combo box. After selecting the name (or ID) I use the find method to locate that record and then populate a form with the correct information. My RSData recordset is the entire table of consumers. The FillFields procedure simply places the appropriate field into the appropriate textbox (see below)
txtFirstName.text = RSData!FirstName
and so on...
You can make your connection as follows:

Set CN = New ADODB.Connection
Set RSData = New ADODB.Recordset
With CN
.Provider = "sqloledb"
.ConnectionString = "driver={SQL Server};server=developsql;uid=;pwd=;database=BRDD"
.Open
End With
RSData.Open "select * from disputeentry order by internalctrlnum", CN, adOpenDynamic, adLockPessimistic

Private Sub cmdEdit_Click()
RSData.MoveFirst
'if editing record by selecting ctrl num, then
'search by combo text
If cboNum.Enabled = True Then
RSData.Find "donateid =" & Val(cboNum.Text) _
, 0, adSearchForward, 1

Else 'search by itemdata ID
RSData.MoveLast
RSData.Find "donateid=" & _
cboName.ItemData(cboName.ListIndex) _
, 0, adSearchBackward, 1
End If
frmDispute.Show
'fill in data for record selected
FillFields
Unload Me

End Sub

Once you've populated the data, you'll need to go from there to save it when changes are made. You might consider a datagrid for the listing of items in stock by customer.
Hope this helps
Stacey

 

[woyler]

Have to agree with Stacy. There are many reasons not to use bound controls. They are easier to code with, but can be extremely cumbersome, especially when you enter into
n-tiered architecture.