Posts by aktharshaik

Private Sub Combo1_Click()
    Set rs2 = New ADODB.Recordset
    rs2.Open "select * from Product where pcode='" & Combo1.Text & "'", cn, adOpenKeyset, adLockPessimistic
    Set MSHFlexGrid1.DataSource = rs2
    If rs2.RecordCount > 0 Then
        Text17.Text = Date
        Text4.Text = rs2!price
        Text12.Enabled = VAL(rs2!pstock) > 0
    End If
End Sub

In the grd_Data_Loader() after opening the connection put this statement.

Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\PharmacyInventory.mdb;Jet OLEDB:System Database=system.mdw;", "admin", ""

    cn.Execute " DELETE FROM CustomerReturn WHERE VAL(quantity) = 0 "

call grd_Data_Loader again after deleting the record

I think that the data type of your quantity field is text. you have to typecast it to number and then check for the records where quantity = 0 and delete them.

the following query will delete all records where quantity is = 0

cn.Execute " DELETE from Soldto where val(quantity)=0"

or if fora a particular cno then u can write

cn.Execute " DELETE from Soldto where val(quantity)=0 and cno = '" & Combo1.Text & "'"

R u getting any error from the procedure mentioned by you?
If yes then what is the error.
or may be try this
Open the query in Dynamic mode

rs1.Open "Select * from Soldto where cno='" & Combo1.Text & "'", cn, adOpenDynamic, adLockOptimistic

PLZ REFER THE ATTACHED PROJECT. I DON'T KNOW WHETHER I HAVE SATISFIED UR EXACT NEED / NOT.

I AM UPLOADING TWO DIFFERENT STYLES OF SOLUTIONS.

one with normal textboxes and another with an array of text boxes. In my opinion the array logic will be very comfortable.

Another Program that can make .chm and .hlp. It is also REALLY easy to use because you write your files in a RichTextBox enviroment. Check it out (HelpMaker):

http://www.vizacc.com/

Also checkout the attached zip file which contains some documentation and references of how to create, open .hlp help files.

Visual Studio has a tool called Help Workshop under Microsoft Visual Studio 6.0 tools. Note: This will work only for WinHelp and not for Html Help.

Start -> Programs -> Microsoft Visual Studio 6.0 -> Microsoft Visual Studio 6.0 Tools -> Help Workshop

You start the Help workshop. Choose New from file menu and select Help Project. Now you will get a Project File name dialog box. You enter the help file name you want to create in Help File text box. You select File option and you get a Topic files dialog box. You click file and choose add and select the help file you have created. Now say OK. Then click Save and Compile button. This will create a .hlp file. Now select Run Winhelp option from file menu of help workshop. This will display the current help project in the file box. You can click the view help to view the help file.

Plz mark the thread as solved.

1. ENSURE THAT THERE WILL BE NO SPACES IN ANY OF THE "ITEM_CODE" VALUES.
2. YOU MUST BE SURE THAT THE FIRST LETTER IS DEFINITELY AN ALPHABET "P" AND THE REMAINING PART OF THE FIELD LOOKS LIKE A NUMBER WITHOUT ANY OTHER CHARACTERS.
3. CONSIDER THE SIZE OF THE FIELD FIRSTLY CHECK THE SIZE OF THE FIELD "ITEM_CODE"

U CAN USE THIS FOLLOWING QUERY

Private Sub Combo1_Click()

    Dim nm As String
    Set rscombo = New Recordset

    Select Case Combo1.Text
        Case "Production Consumable"
            'In this query replace 10 with a value equal to (size of your ITEM_CODE field - 1)
            rscombo.Open "SELECT MAX(MID(PQUERY.ITEM_CODE, 2, 10)) AS MAXID FROM PQUERY", con, adOpenDynamic, adLockOptimistic
            MsgBox (rscombo.Fields(0))
            'Here rscombo!MAXID is the last number. To show the next required number
            'just add 1 to it and display. follow the same logic for others also
            List1.AddItem rscombo!MAXID + 1

        Case "Maintenance Consumable"
            rscombo.Open "select * from mquery", con, adOpenDynamic, adLockBatchOptimistic

        Case Else
            rscombo.Open "select * from pquey", con, adOpenDynamic, adLockOptimistic
    End Select
End Sub

IF YOUR ITEM_CODE FIELD HAS MULTIPLE TYPES OF VALUES LIKE "P199", "S23", "S76", "U876" U HAVE TO MODIFY THE QUERY AS BELOW

rscombo.Open "SELECT MAX(MID(PQUERY.ITEM_CODE, 2, 10)) AS MAXID FROM PQUERY WHERE LEFT(PQUERY.ITEM_CODE,1) = 'P' ", con, adOpenDynamic, adLockOptimistic
'OR
    rscombo.Open "SELECT MAX(MID(PQUERY.ITEM_CODE, 2, 10)) AS MAXID FROM PQUERY WHERE LEFT(PQUERY.ITEM_CODE,1) = 'U' ", con, adOpenDynamic, adLockOptimistic

Try this one.

strSql = "SELECT mrproduction.mrStkNum, mrproduction.mrLotNum, mrproduction.mrQty, fglotnum.mrCost FROM mrproduction INNER JOIN fglotnum ON mrproduction.mrLotNum=fglotnum.mrLotNum"
      rs.Open strSql, cn, adOpenStatic, adLockOptimistic, adCmdText
      If rs.BOF = False Then
           rs.MoveFirst
      End If

also can add sorting order clause

strSql = "SELECT mrproduction.mrStkNum, mrproduction.mrLotNum, mrproduction.mrQty, fglotnum.mrCost FROM mrproduction INNER JOIN fglotnum ON mrproduction.mrLotNum=fglotnum.mrLotNum ORDER BY mrproduction.mrStkNum, mrproduction.mrLotNum"
      rs.Open strSql, cn, adOpenStatic, adLockOptimistic, adCmdText
      If rs.BOF = False Then
           rs.MoveFirst
      End If

You can even add sort clause if u like to the same

TO SORT BY DATE

SELECT sNAME, sDATE, sTK FROM (SELECT Table1.name AS sNAME, Table1.date1 AS sDATE, Table1.tk AS sTK from Table1 UNION ALL SELECT Table2.add AS sNAME, Table2.date2 AS sDATE, Table2.tk2 AS sTK FROM Table2) ORDER BY sDATE

OR

TO SORT BY NAME AND DATE

SELECT sNAME, sDATE, sTK FROM (SELECT Table1.name AS sNAME, Table1.date1 AS sDATE, Table1.tk AS sTK from Table1 UNION ALL SELECT Table2.add AS sNAME, Table2.date2 AS sDATE, Table2.tk2 AS sTK FROM Table2) ORDER BY sNAME, sDATE

Check out the attachment.

MODIFY THE QUERY AS GIVEN BELOW

SELECT sNAME, sDATE, sTK FROM (SELECT Table1.name AS sNAME, Table1.date1 AS sDATE, Table1.tk AS sTK from Table1 UNION ALL SELECT Table2.add AS sNAME, Table2.date2 AS sDATE, Table2.tk2 AS sTK FROM Table2)

I have attached back ur file with the same change. plz have a look at it.

USE THE OBJECT CurrentDb.

Public Sub GetMaxCRID()

    Dim Vtemp As Integer

    On Error GoTo GetMaxCRID_Error

    'Here first we check whether or not if any record exists in the table
    If CurrentDb.OpenRecordset("SELECT COUNT(CRID_INDEX.CRID_NUM) FROM CRID_INDEX")(0) > 0 Then
        Vtemp = CurrentDb.OpenRecordset("SELECT MAX(CRID_INDEX.CRID_NUM) FROM CRID_INDEX")(0)
    Else
        Vtemp = 0
    End If
    'Now you have the maximum number in the field CRID_NUM

GetMaxCRID_Done:
    Exit Sub

GetMaxCRID_Error:
    MsgBox "An error has occured in procedure GetMaxCRID." & vbCrLf & vbCrLf & "Error No: " & Err.Number & vbCrLf & Err.Description
    Resume GetMaxCRID_Done

End Sub

If SQL Server use the query below

dr.Execute "ALTER TABLE results12 ADD Total Numeric, Percentage Numeric, Status char(5)"

By the way what is type of variable dr ? is it ADODB.Connection?

It worked for me with VB 6.0 and MS-Access Database.

What is the Database Backend u r using??

Also change this statement

List1.AddItem rscombo.Fields("item_code")

or

List1.AddItem rscombo!item_code

If rscombo.BOF = False Then

This statement is to check if any record bearing that item_name has been retreived/not. if BOF property is True then there are no records satisfying the criteria.

Private Sub Combo1_Click()

    Dim nm As String

    Set rscombo = New Recordset

    Select Case Combo1.Text

        Case "Production Consumable"
            rscombo.Open "select * from Pquery where item_name ='" & Combo1.Text & "'", con, adOpenDynamic, adLockOptimistic
            If rscombo.BOF = False Then
                MsgBox (rscombo.Fields(0))
                List1.AddItem rscombo!Fields(item_code)
            End If

        Case "Maintenance Consumable"
            rscombo.Open "select * from mquery", con, adOpenDynamic, adLockBatchOptimistic

        Case Else
            rscombo.Open "select * from pquey", con, adOpenDynamic, adLockOptimistic

    End Select
End Sub

Filter your query by comparing the field related to the combobox text,
may be something like this,

rscombo.Open "select * from Pquery where item_name ='" & Combo1.Text & "'", con, adOpenDynamic, adLockOptimistic

It must be

dr.Execute ("alter table results12 add Total number,Percentage number,Status char(5)")

remove the Parenthesis for the Fields list provided.

Try this piano program in VB.

Write the code for exchanging the keycodes in the KeyDown Event of the rtb.

Plz Post your query here. will help u out.

Plz post the procedure u have coded for the AddNew button.

and for the other error also for the Show button

Can u plz post the sample code where u r populating the flexgrid with the records?

can u plz upload the files to check?

Could not get your exact requirement from your post. plz use the below checklist to be more clear for your req.

1. Please can u give some test values for the values in the ListBox 1 (approx. 10-12 is enough)
2. The numbers in the 7 text boxes.
3. and the Required result from those values.

Plz check out the following attached file.

In which appliation R U writing VBA code? MS-Access or excel?
Where is the table from which u want to read the fields from? Is it an Excel Spreadsheet or an Access Database or some other DB?

Please make some clarity in your problem and will help to solve it out precisely.

If ur problem is solved then Mark the thread as solved, b'cos it may help even the others to search for similar problem and those threads having the notation of solved.

a better suggestion is change the name of the field in every table with the fieldname "date" as "mdate" or "edate" or "ddate" or anything else , other than the spelling "date"

Hey Got the problem.

Actually the field with the name "date" is actually a reserved keyword.

just put square brackets around that field in the query.

her it is

"Insert Into Deliver (scode, pcode, quantity, unit, amount, pcarrier, pterms, drnumber, [date]) Values ('" + Combo1.Text + "','" + Combo2.Text + "','" + Text1.Text + "','" + Text2.Text + "','" + Text3.Text + "','" + Text4.Text + "','" + Text5.Text + "','" + Text6.Text + "',#" + Text7.Text + "#)"

All the fields in the Deliver table are Text except for the Date Field.

"Insert Into Deliver (scode, pcode, quantity, unit, amount, pcarrier, pterms, drnumber, date) Values ('" + Combo1.Text + "','" + Combo2.Text + "','" + Text1.Text + "','" + Text2.Text + "','" + Text3.Text + "','" + Text4.Text + "','" + Text5.Text + "','" + Text6.Text + "',#" + Text7.Text + "#)"

i have enclosed a screenshot of the reply screen

just zip the access database file and the vbform.

Click on Reply to Thread button. do not post in quick reply.
below the reply box u can find the button for attachments. there u select the zip file and upload it.

If the database is MS-Access u have to use a # for date field.
It goes like this. replace the query with this one.

"Insert Into Deliver (scode, pcode, quantity, unit, amount, pcarrier, pterms, drnumber, date) Values ('" + Combo1.Text + "','" + Combo2.Text + "'," + Text1.Text + ",'" + Text2.Text + "'," + Text3.Text + ",'" + Text4.Text + "','" + Text5.Text + "','" + Text6.Text + "',#" + Text7.Text + "#)"

Given below are some of the threads where u may find a few attachments of project, code snippets, little documentation etc. done by me

http://www.daniweb.com/forums/thread154332.html
http://www.daniweb.com/forums/thread143320.html
http://www.daniweb.com/forums/thread142705.html
http://www.daniweb.com/forums/thread142516.html
http://www.daniweb.com/forums/thread141488.html
http://www.daniweb.com/forums/thread142073.html
http://www.daniweb.com/forums/thread141890.html
http://www.daniweb.com/forums/thread140753.html
http://www.daniweb.com/forums/thread140868.html
http://www.daniweb.com/forums/thread140757.html
http://www.daniweb.com/forums/thread139758.html
http://www.daniweb.com/forums/thread140572.html
http://www.daniweb.com/forums/thread140446.html
http://www.daniweb.com/forums/thread140151.html
http://www.daniweb.com/forums/thread140151.html
http://www.daniweb.com/forums/thread139647.html
http://www.daniweb.com/forums/thread139647.html

You can download the files and have a look at them.

Regards
Shaik Akthar

Download the attachment i have given here. some help might be there in programming with the database.

http://www.daniweb.com/forums/post731423-8.html

Regards
Shaik Akthar

Where do u want the records to be printed? Is it a printer? or only on the console (screen)?

You can very much use FlexGrid/Hierarchical FlexGrid control to display the data from the database onto the screen in the form of a table.

Regards
Shaik Akthar.

http://www.connectionstrings.com/?carrier=sqlserver2005

Regards
Shaik Akthar

What is the programming frontend u r using?
.NET or VB6

Regards
Shaik Akthar

Try this link
http://www.connectionstrings.com/?carrier=sqlserver2005

Regards
Shaik Akthar

The Logic for the removal of the related records in the database can be written just before the Remove event of the ListView. After all matched Items are done, U will have the database updated as well as the ListView and I think there is no need to Reload the ListView, which involved again querying the DB. Alternatively it can be done to re-confirm if the records are really deleted from the DB or not.

Regards
Shaik Akthar

Try this code

Dim iVal As Integer
    Dim lCount As Integer

    lCount = li.ListItems.Count
    For iVal = 1 To lCount
        If iVal > lCount Then Exit For
        'If u want to compare to the First Column
        If Trim(li.ListItems(iVal).Text) = Trim(Text1.Text) Then
            li.ListItems.Remove (iVal)
            iVal = iVal - 1
            lCount = lCount - 1
        End If

''If u want to compare to the other Column use SubItems
''SubItems(1) is 2nd col, SubItems(2) is 3rd col and so on...
'        If Trim(li.ListItems(iVal).SubItems(1)) = Trim(Text1.Text) Then
'            li.ListItems.Remove (iVal)
'            iVal = iVal - 1
'            lCount = lCount - 1
'        End If
 
   Next

also while comparing if u don't want it to be case sensitive use the UCASE() function

If UCase(Trim(li.ListItems(iVal).Text)) = UCase(Trim(Text1.Text)) Then

Regards
Shaik Akthar

just once give me all the fields with their datatypes with which they exist in the database in the table Deliver

what is the datatype of the field 'date' in your database and is it MS-Access Database?

ur code is

"Insert Into Deliver (scode, pcode, quantity, unit, amount, pcarrier, pterms, drnumber, date) Values ('" + Combo1.Text + "','" + Combo2.Text + "'," + Text1.Text + "," + Text2.Text + "," + Text3.Text + "," + Text4.Text + "," + Text5.Text + "," + Text6.Text + "," + Text7.Text + ")"

I assume scode, pcode, unit, pcarrier, pterms, drnumber, date are Text fields
and quantity, amount are Numeric fields
hence

"Insert Into Deliver (scode, pcode, quantity, unit, amount, pcarrier, pterms, drnumber, date) Values ('" + Combo1.Text + "','" + Combo2.Text + "'," + Text1.Text + ",'" + Text2.Text + "'," + Text3.Text + ",'" + Text4.Text + "','" + Text5.Text + "','" + Text6.Text + "','" + Text7.Text + "')"

also remove the
, cn, adOpenKeyset, adLockPessimistic
in the insert statement.

Regards
Shaik Akthar