Insert Data From Dbf Into Combobox
Hi All,
I wish to insert data from a database file into a combo box using visual basic 6.0.
Please let me know the correct method / coding for the same.
Thanks.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Insert Data Into Access Using Combobox And List Box
rs.Open "INSERT INTO BookReturnTable(Book-ID, Student-ID, ReturnDate, Status, Fine, AmountPaid)Values ('" & TxtBookId1 & "', '" & Text1 & "', '" & Text2 & "' , '" & x & "', '" & TxtTitle & "', '" & TxtAuthor & "')", cn
I'm using such statement to insert the data but no idea in inserting the data using list box and combo box...Need some guidance as well as some example...Thank you
Insert Sql Wont Insert Data After Decimal
i have a form that uses a Calculation script to do the vat calculation
Code:Option Explicit ' Requires variables to be declared before they are used
Rem * Declarations ***********************************************************
Dim dblNumber As Double 'Number Entered by user
Dim dblResult As Double 'Result of calulation
Dim intOption As Integer 'Operator held (+,-,*,/)
Dim dblMemory As Double 'Number stored in the Memory
Dim blnOption As Boolean 'Number","+","."
Dim blnEqual As Boolean 'Equals error
Dim blnMemory As Boolean 'Memory error
Dim blnError As Boolean 'If Point '.' is press more than once
Dim strVat As String 'VAT user number entered
Dim dblTotal As Double 'VAT Total
Dim strAnswer As String 'VAT Answer
Public Sub VATCALC()
On Error Resume Next
dblTotal = (Invoicesto.Price.Text * Invoicesto.Vatpc.Text) / 100 '* strVat '(txtSubTotal.Text * 17.5) / 100
Invoicesto.exvat.Text = Invoicesto.Price.Text - dblTotal 'Round(strAnswer, 2)
End Sub
VATCALC works, however when i add the product to the table the calulations have no decimal and data after it.
Insert ComboBox In Datagrid
Does anyone knows if is possible to insert combo box into the cells of a datagrid or mshflexgrid.if it is possible how can I do it . Thanks
How To Insert Combobox Value Into Textbox?
i need to design a form which will have a combobox with the names of the employees,as soon as a name from the combobox is selected, the designation of that employee has to be shown in the textbox of the same form.
please help me out.
regards
Insert List From ComboBox To ListBox
I'm currently making an excel sheet called ("Data Sheet").
When I select a data in my ComboBox from my UseForm I would like that all data corresponding to this selection will appear in my ListBox.
Example:
In my ComboBox I have years and in my ListBox I have names.
I would like that if I choose a specific year, all names corresponding to that specific year will appear in my ListBox.
My name selection starts on range A2 and my year selection starts on range J2.
How can I do that?
Thank u very much
Combobox Select/Insert Code
Hi all,
I have a combobox on my form. The combo is currently populated by values from a table in a DB. I have two questions:-
1- Suppose I need to select more than 2 items in the dropdown, how can I ascertain the selected items?
2 - I want my users to check in the combobox dropdown if what they are looking for is already available in the DB if NOT available, then I want to allow them to type/key in the missing item directly in the combobox is this possible?
If the answer is yes, please let me have a sample code of how I can code that.
Thanks
Insert A Combobox In A Dynamic Listview As Subitem
Hi,
I created a “dynamic” listview (I mean, the number of columns, the width of the ColumnHeaders depends of the code).
I use the kind of code to populate the listview
ListView1.View = lvwReport
ListView1.ColumnHeaders.Add , , "Id", 60
ListView1.ColumnHeaders.Add , , "Type", 60
ListView1.ColumnHeaders.Add , , "Link", 60
ListView1.ColumnHeaders.Add , , "Name", 100
ListView1.ColumnHeaders.Add , , "Direction", 60
cpt = 0
For intx = LBound(ArrTest) To UBound(ArrTest)
ListView1.ListItems.Add , , ArrTest(cpt).ID_TRC
ListView1.ListItems(cpt + 1).SubItems(1) = ArrTest(intx).TYPE
ListView1.ListItems(cpt + 1).SubItems(2) = ArrTest(intx).LINK
ListView1.ListItems(cpt + 1).SubItems(3) = ArrTest(intx).NAME
ListView1.ListItems(cpt + 1).SubItems(4) = ArrTest(intx).DIREC
cpt = cpt + 1
Next intx
But I can use the same listview at another place in the code with different columns and width. So, that why is a “Dynamic Listview”.
My actual problem is I want to replace (for example) the SubItems(4) by a combo box, so I can change the value of the SubItems(4) in the listview before updating the Array (ArrTest).
Anybody have an idea how to realize this things
See the attach jpeg file for an example of what I want to do
Many Thanks
Dominic Lavoie
SQL Insert Into Doesn't Insert Any Data
I have couple of problems. I am creating a MP3 player that scans the HD for MP3's and puts them into an Access database (XP, hope this doesn't make a difference). Anyway, I can use this method below to get the data into the database:
Code:
Data2.Recordset.AddNew
Data2.Recordset.Fields("Artist") = Trim(T1)
Data2.Recordset.Update
Data3.Recordset.AddNew
Data3.Recordset.Fields("Artist") = Trim(T1)
Data3.Recordset.Fields("Album") = Trim(T2)
Data3.Recordset.Update
Data1.Recordset.AddNew
Data1.Recordset.Fields("Artist") = Trim(T1)
Data1.Recordset.Fields("Album") = Trim(T2)
Data1.Recordset.Fields("Title") = Trim(T3)
Data1.Recordset.Fields("Genre") = Genre
Data1.Recordset.Fields("Year") = Year
Data1.Recordset.Fields("AudioLink") = T4
Data1.Recordset.Update
And it will actually get the data into the tables and fields that I need exactly. But my problem with this is I read the records in thru SQL:
strSQL1 = "SELECT [Album].* FROM [Album] WHERE Artist= '" & T1_A & "' ORDER BY [Album].[Album]"
Set RS = DB.OpenRecordset(strSQL1, dbOpenDynaset)
Set Data2.Recordset = RS
But when I select a record, it will give me a run-time error 3075. If I comment out the "Set" commands and put in say a MsgBox (strSQL1) instead, it will display the following:
SELECT [Album].* FROM [Album] WHERE Artist='Britney Spears
And nothing else after the name. It should have a ' ORDER BY [Album].[Album] following, but not matter what I try it won't.
If I go into the database and retype the exact same information on top of what was there, it will then work fine, example, if the recordset adds the record "Britney Spears" to the database with the code at the top, and I retype it, it will then work perfectly.
So instead of using the recordset methods, I decided to try and use the SQL Insert Into command:
sqlSTR5 = "INSERT INTO [Artist] (Artist) VALUES ('[" & Replace(T1, "'", "''") & Chr(34) & "]')"
sqlSTR6 = "INSERT INTO [Album] (Artist, Album) VALUES ('[" & Replace(T1, "'", "''") & "]','[" & Replace(T2, "'", "''") & "]')"
sqlSTR7 = "INSERT INTO [Main] (Artist, Album, Title, AudioLink) VALUES ('" & Replace(T1, "'", "''") & "', '" & Replace(T2, "'", "''") & "', '" & Replace(T3, "'", "''") & "', '" & Replace(T4, "'", "''") & "')"
Data2.Database.Execute sqlSTR5
Data2.Refresh
Data3.Database.Execute sqlSTR6
Data3.Refresh
Data1.Database.Execute sqlSTR7
Data1.Refresh
But the problem I get with this is that it searches through all the records, but when I look at the actual database, nothing is in it at all, for any table or records.
Any help would be greatly appreciated!!!!
Thanks, Corey
Searching Specified Folder, Getting Name Of File Before Period, Insert Into Combobox.
Hi guys,
I have been working hard on my application and I am not able to figure something out.
Let me explain the best I can:
My application uses Access databases as a backend. I am looking to store multiple databases in a central location on a computer. Lets say in the installation folder under a folder called Database.
So if the app is in c:program filesmy app
the mdb files would be in c:program filesmy appdatabase
What I am looking to do is get the file name of all the .mdb files and insert those filenames (excluding .mdb) into a comboxbox for the end user to be able to select which database they want to use.
Any suggestions on how I should go about doing this would be highly appreciated. Thanks in advance for any suggestions or assistance.
Insert Data Into Flexgrid Where Date Or Data Equals Value Of MonthView
im trying to insert records into a FlexGrid where the date of the records is equal to the date of the Month View; but whenever the form loads or the date is clicked no records come up.
(the code may be right and it may just be another part of the program that is ruining it but either way im stumped.)
The code im using for both form_load and date_click is:
With dtaManager
If Len(App.Path) <= 3 Then
.DatabaseName = App.Path & "Manager.mdb"
Else
.DatabaseName = App.Path & "Manager.mdb"
End If
query = "select Date,Time,Name,Street,Town,"
query = query & "State,PCode,"
query = query & "Phone from Manager "
query = query & "where Date = '*" & MVCalender.Value & "*' order by Time"
.RecordSource = query
.Refresh
End With
MSDates.Refresh
Help if u can PLease
This stuff can really get to u :(
Thanks,
Monks
How To Insert Temporary Data In Access(table) Using Data Grid
How to insert data into a table using a data grid.
A little background,i am using SS TAB in my forms i need to hold data temporarly im my data grid
and then finaly at click it should be inserted in the table.The SS TAB is linked to ADOC,which is inturn
linked to the table in access.
How To Insert Temporary Data In Access(table) Using Data Grid
How to insert data into a table using a data grid.
A little background,i am using SS TAB in my forms i need to hold data temporarly im my data grid
and then finaly at click it should be inserted in the table.The SS TAB is linked to ADOC,which is inturn
linked to table.
Edited by - manav74 on 10/29/2003 1:01:07 AM
What Is The Best Way To Submit Data To Server And Insert Data To Database?
In my program, according to users action it will generate some data, now I want to:
insert data to a database on server
when users submit data, they should have an ID&password, I should be able to check it.
now, what I am doing is:
1) users submit data to server by ftp;
2) a NT service on server monitoring ftp files, if new files are uploaded, then read the file and insert the data to database;
Is there any better way to do this?
Insert Data Into A Accees Data Base
how can i insert data into a access database .
ie defining the connections and the query???
when opportunities arises ,only the prepared mind is fovoured
How 2 "insert" Items In A Combobox List (Works Like A Charm - Thanks!)
When filling a list in a combobox, new items go at the end of the list. Are there any combobox properties which allow me to insert new items at the beginning of the list.
I can write code to down load the list contents, clear the list and then reload the items in reverse order. Are there easier ways to accomplish this?
I tried searching but was unable to phrase a question which gave any results.
As always, any help is greatly appreciated.
jj2
Insert Data
Do you know the sql query if I want to insert some fields in a new table from 5 different tables. These 5 tables have one common field (nycuid). I used following query but getting error.
insert into collectall
select nycuid, nycName, nycAddress, nycDunsNumber
union
select associd, assocName, assocAddress, assocDunsNumber
union
select duid, duName, duAddress, duDuns
union
select guid, guName, guAddress, guDuns
union
select hpid, hpName, hpAddress, hpDuns
from nysCompany, assocCompany1, duCompany1, guCompany1, hpCompany1
where
nysCompany.nycuid=assocCompany1.nycuid and
nysCompany.nycuid=DuCompany1.nycuid and
nysCompany.nycuid=guCompany1.nycuid and
nysCompany.nycuid=hpCompany1.nycuid
Insert Data Into Db
Hi , How are you my brothers?
I have access database called "db" ... I put it in my site : http://mysite/db.db
now I want a vb code that insert (or delete or update) data in this database file .
how can I do that ?
Insert Data Into Db
I am wanting to change a value in one of the field in my database. Here is my code. I get an error "No value given for one or more required parameters." Could someone tell me what I have wrong in my code?
Code:
Dim c As Integer
Dim oConn As ADODB.Connection
Dim sInsert As String
Dim MyRecSet As ADODB.Recordset
Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "Employeedb.mdb;"
Set MyRecSet = oConn.Execute("SELECT EName") 'Error on this line.
MyRecSet.MoveFirst
Do While Not MyRecSet.EOF
For c = 0 To MyRecSet.Fields.Count - 1
If MyRecSet.Fields("EName") = Empcmb.Text Then
sInsert = "INSERT INTO Edata (HrsLeft, ) VALUES('" & PTOYtxt.Text & "')"
oConn.Execute sInsert
End If
Next c
MyRecSet.MoveNext
Loop
oConn.Close
Need To Insert Data That Contains ' In SQL
Hi, I have to enter data that contains a ' inside it like Ala'a , the problem that the ' is required in the sql query , for example to enter a string called ABCD the query is :
VB Code:
insert into table values ('ABCD')
so how to do this for the string Ala'a
SQL Does Not Insert Data
Hi!
I'm trying to insert data from the textbox but it does not work. I have compiled it but nothing seems to be saved in the database.
Here is what I used:
strSQLName = "INSERT INTO ClientCode(Name) SELECT * FROM ClientCode WHERE Code = '" _
& cboClientCode.Text & "'" & "VALUE('" & txtClientName.Text & "')"
Cheers
Insert New Data
VB6, Access2000 Db.
Two .mdb's - MyDb1.mdb, MyDb2.mdb
Goal:
Branch office sends in a file everyday, with weekly history (MyDb2.mdb).
I would like to update this history (on a daily basis), into a transaction mdb (MyDb1.mdb)
I dont want to go through each item with a recordset, because I only need the transactions which are NOT in MyDb1.mdb yet.
I am getting a syntax error with the Insert statement.
VB Code:
Dim cn1 As ADODB.Connection Set cn1 = New ADODB.Connection cn1.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Replace$(App.Path & "DbMyDb2.mdb", "\", "") & "; " & _ "Jet OLEDB:Database Password=MyPassword;" cn1.Execute "INSERT INTO tblData AS T1 WHERE NOT EXISTS (SELECT Index FROM tblData IN '" & _ Replace$(App.Path & "TempMyDb1.mdb", "\", "") & "' WHERE Index <> T1.Index)", _ , adExecuteNoRecords cn1.Close Set cn1 = Nothing
Can anyone help me with this...?
Insert Data
Hi ,
i have a table with column a1, a2, a3, a4, a5.
I can get any data from 1 to 5.
If i get 3 i need insert it into a3, and 5 into a5.
I will appreciate for help.
Insert Data To Excel
hi..
i have a recordset with some data from an sqlserver
i want to insert that data to an excel sheet..
the excel sheet already contains some data in the first two
rows. i want to insert the data from recordset without losing
the exisiting data.
please help
Insert Data Into Frequency?
hey all,
i was wondering if it is possible to insert data into the unused frequencies of a wav file. for eg. if i have a small file, say a whistle which is at 1k hz. now if i want to add some data of my own into the 10k band (which is unused) then is it possible in vb? if i need to understand a transformation algorithm or something let me know....im actually trying to creat an audio stego prog. i have created a program which modifies the random bits wrt time. this was fairly simple and creates very good, undetectable carriers. i want to give users the option of frequency mod.
if anyone is interested, the program is downloadable at
http://www.angelfire.lycos.com/ky3/an/wavstego.zip
if the link doesnt work type it out in the title bar nd it should be fine
Comments/Suggestions/Insults welcome. anyone who can crack it please let me know how u did it. anyway that about covers it...thanks
kyan_p@hotmail.com
Insert OLE-Field Data Via ADO / DAO
Hello! Is it possible to insert an OLE-object (bitmap) into an Access DB OLE-Field via ADO or DAO?
To err is human, to really foul things up requires a computer.
Insert Data... Syntax???
Hi,
Hoping someone could help me with a seemingly simple insert problem. I am wanting to insert time into a table. Proper syntax would be greatly appercitated. Nothing i have tried seems to wanna work currently i have
Dim conStart As New ADODB.Connection
Dim rstStart As New ADODB.Recordset, sql As String
Dim cmdReStart As New ADODB.Command
conStart.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;" & "data Source =" & strComDialog
With rstStart
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.ActiveConnection = conStart
.Open "Select startTime from RouteStart WHERE RouteStart.RouteID = " & strCurrentRoute & " ORDER BY startTime"
End With
Set DataStart.DataSource = rstStart
i wanna update the startTime with a new data
Any help??
Insert Data In Textfield Into DB
hi i am new to vbscript.
I have a database and in one of the form there is a textbox for user to enter remarks. How can i use vbscript to insert this remark into the database table?
Thanks in advance
Lifang
Can You Mix Text And Data In Insert Into
this is my code and keep getting syntex error
Private Sub Command3_Click()
MyConn.Execute "INSERT INTO distance(place id,miles,yards)VALUES('" & Text6(1).Text & "'," & Text7(2).Text & "," & Text8(3).Text & ")"
End Sub
place id is text the other 2 are numbers
cheers
Doug
Best Way To SELECT And INSERT Data Using SQL
Hello,
I have seen some people use the dbconnection.execute method to select data from a database but also the recordset.open method.
I can see that using the dbconnection.execute is fine for INSERT, UPDATE etc as no recordset is required, but why is the recordset.open method better for selecting data, I normally use the .execute for everything.
Thanks
Insert Data In Database
Hello!
What is the best way to insert 100 vars with the sql command Insert ?
The 100 vars are in one single table.
Insert Data From One Server To Another
I am using vb6 and sql server 2000.
I want to pull data out from a table in server 1 and insert it to the same table in server2.
I know I can do it using a loop go through the recordset to insert one by one.
But is there anyway I can insert about 3000 records at once?
Any input is haighly appreciated!
How Use Mshflexgrid To Insert Data
I am using a Mshflexgrid with an indeterminate number of lines for 3 columns, and it wanted to add the data that are in this Mshflexgrid in a MSAccess table.
Only it is inserting the last register of the Mshflexgrid.
Thx.
Insert Data In ROM Memory
Helllo all im using an MScom to read a Rom memory
my problem is I want to insert a data while Reading the Rom Memory then
program the original Rom memory plus the inserted data... is that possible?
Im planning to save first the Rom memory in temporary array while reading i dont how..
Please Help
Insert Data To Different Datasource
here is the scenario:
i have a connection established to a data file and a connection established to a sql database. both are connected using ADO.
is it possible to do an insert to the sql database based on a select statement to the data file? would like to do this without looping the datafile recordset if possible.
thanks.
SQL INSERT Data Between 2 Databases
Hi there,
I want to insert data from table A to table B.
Both tables are part of a different database.
The record structure of both tables are the same.
e.g:
Set dbs = OpenDatabase("R:VB SourcesDB1.mdb")
Set dbs2 = OpenDatabase("R:VB SourcesDB2.mdb")
dbs.Execute "INSERT INTO B SELECT * FROM [A]"
I am able to insert the data when both tables are part of the same database.
I don't know how to insert the data when the tables are part of a differt database.
Anybody a clue ??
Many thanx!!
HELP! How To Use INSERT With DAO Data Control?
I need to use the SQL statement INSERT INTO on my data control using DAO with an Access database. All of the exmaples show how to use the SELECT statement in the RecordSource property and doing a Refresh. But using any action statements like instert, delete, etc. causes an error of invalid operation or soemthing.
Is there a different way to run these type of SQL statements with the Data control as DAO?
I'm stuck...
Thanks!!!!!!!!!!
Insert Data To Datagrid
to insert data to datagrid, we have to define the datasource and datamember. and we also need to specify the field member on each column.
but what if i have data that are from many different tables (but still have fixed number of columns in datagrid). i retrieve data at run time and want to put them onto the datagrid at that time.
the reason doing this is because i have to based on different criteria to get data. and, hence, i know what table i need to use ONLY at run time.
can anyone give advice?
thanks
Insert Data To Datagrid
to insert data to datagrid, we have to define the datasource and datamember. and we also need to specify the field member on each column.
but what if i have data that are from many different tables (but still have fixed number of columns in datagrid). i retrieve data at run time and want to put them onto the datagrid at that time.
the reason doing this is because i have to based on different criteria to get data. and, hence, i know what table i need to use ONLY at run time.
can anyone give advice?
thanks
Can't Insert Data To MySQL With VB6
Hello I'm trying to use the code below to insert data into mysql table. I get an error at the code in red. Anyone have the idea why? Please help.
Private Sub cmdMenu_Click()
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
'Set conn = Nothing
conn.CursorLocation = adUseClient
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=server_address;DATABASE=db_name;" _
& "UID=my_id;PWD=my_pw;OPTION=1"
conn.Open
'Adding records to table
conn.Execute "INSERT INTO CUSTOMERS(Customer_ID,Customer_Name, Customer_Address,Email,Telephone," _
& "Mobile, Fax)VALUES('NULL','"TxtName.Text "','"TxtAddress.Text"','"TxtEmail.Text "'," _
& "'"TxtTelephone.Text"', '"TxtMobile.Text "','"TxtFax.Text "');"
End Sub
Insert Data In Mysql With Vb
hello guys,
I'm new in this game. Could you please help me with the coding for insert into MySQL database using a visual basic code. Thanx in advance
How To Insert Data From A Table Using Sql?
I would like insert a data to a table but the other field in the table are located on the other table. how can i merge the datas into one table?
sample illustration:
tblcustomer: tbldelivery:
custcode trackno
custname shipdate
custadd custcode
custname
how can i merge the table on tblcustomer going to tbledelivery?
Truncate And Insert Data
Thanks for your help. successful for my code.
But i have some question again like before.
This is code :
Private Sub DTPicker2_CloseUp()
On Error GoTo pas
Adodc1.RecordSource = "truncate history_grafik"
Adodc1.Refresh
pas:
If Err = 3704 Then
End If
End Sub
and the same problem:
Private Sub periode_Click()
On Error GoTo try
Z = Format(Me.DTPicker1, "yyyy-mm-dd")
z1 = Format(Me.DTPicker2, "yyyy-mm-dd")
Adodc1.RecordSource = "insert into History_grafik select distinct kd_plj, kd_kelas, kd_penerbit, tgl_pr, deadline, keterangan from jadwal_pr where tgl_pr between '" & Z & "' and '" & z1 & "' "
Adodc1.Refresh
Exit Sub
try:
If Err = 3704 Then
End If
cmbGrafik.Enabled = True
End Sub
If I click "DTPicker2" and commandbutton "periode" i get instruction again:
"Operation is not allowed when this object is closed?"
I try to add this code => "Exit Sub" like above, but fail. The instruction is appear again.
VeenaMG or someone Can help me?
Thanks for the help.
Save Insert Data
I have some code:
Private Sub Command1_Click()
On Error GoTo try
ado_mapel.RecordSource = "insert into mapel (kd_plj, nama_plj) values('" & kd_plj.Text & "','" & nama_plj.Text & "')"
ado_mapel.Refresh
With ado_mapel.Recordset
.AddNew
!nama_plj = nama_plj.Text
!kd_plj = kd_plj.Text
End With
try:
If Err = 3704 Then
End If
End sub
I will save my data into database. After finish to input my data then I click CommandButton, I find error 3704 and get instruction like this: “Operation is not allowed when the object is closed”. In fact, my data is fill in database, but why the instruction is appear?Anyone can help me for disappear this instruction? Thanks before.
Listvieuw Insert Data
at the moment i've git this code and it works fine
Private Sub Command2_Click()
Dim clmX As ColumnHeader
Set clmX = ListView1.ColumnHeaders. _
Add(, , "IO", ListView1.Width / 10)
For intI = 1 To 16
strIO = GetINI("IO", "IO" & intI, "?")
If strIO = "input" Then
Set clmX = ListView1.ColumnHeaders. _
Add(, , intI, ListView1.Width / 40)
End If
Next intI
ListView1.View = 3
no meightby a simple question, but is there a way to add values into certain
collomns.
thanks Maarten
Insert Data Into A Database
I have succeeded to insert data into the correct fields in the database, BUT i am having alot of trouble with putting in data in a date/time format.
I have the data in the following fields...
Example:
18-12-03 15:59:25
The fields
DateDay = 18
DateMonth = 12
DateYear = 03
TimeHour = 15
TimeMin = 59
TimeSec = 25
I want to insert that data into the field DateTime (format dd-mm-yy hh:nn:ss) nn = minutes
So any help/ideas?
INSERT INTO table
(number, datetime)
VALUES (15, xxxxxxxx)
Insert Data Using Datagrid....
Hi All,
I am using Vb6.0 and Access 97
On a form i have Datagrid. User will enter amount (Only amount).
The program has to update amount table : amount, userID (Multiple users will use this table)
I display totals at bottom.
Total 1 is total amount user has to enter. (will not change)
Total 2 is total amount user entered in grid.
as user moves to next row the second total should be updated
Mean while if user entered wrong amount and wants to modify.... User will scroll back to that row and clicks on that row(amount) and will modify and moves back to last row.
My problem is with modifying data.... current code will insert and displays totals correctly.....
Code:
Private Sub Form_Load()
cAmt = 0
Set rsCheckamts = New ADODB.Recordset
Set rsTotal = New ADODB.Recordset
Set rsSystemChkTotal = New ADODB.Recordset
'Display any existing check amounts if any
If rsCheckamts.State = adStateOpen Then rsCheckamts.Close
rsCheckamts.Open "SELECT tbl_CheckAmounts.Amount " _
& " FROM tbl_CheckAmounts WHERE tbl_CheckAmounts.User_ID = '" & strUserName & "' ", _
conBreakdown, adOpenStatic, adLockOptimistic, adCmdText
Set grdChkAmts.DataSource = rsCheckamts
GridResize
grdChkAmts.Refresh
grdChkAmts.AllowAddNew = True
grdChkAmts.AllowUpdate = True
'To display Deposit total (lblDTotal)
If rsSystemChkTotal.State = adStateOpen Then rsSystemChkTotal.Close
rsSystemChkTotal.Open " SELECT Sum(TAXPAYER_CHECK.AMOUNT) AS SumOfSystemChk, " _
& " Count(TAXPAYER_CHECK.AMOUNT) AS TotalChecks, " _
& " SUBMISSION.USER_ID, SUBMISSION.DATE " _
& " FROM SUBMISSION INNER JOIN TAXPAYER_CHECK ON " _
& " SUBMISSION.SUBMISSION_ID = TAXPAYER_CHECK.SUBMISSION_ID " _
& " GROUP BY SUBMISSION.USER_ID, SUBMISSION.DATE " _
& " HAVING (((SUBMISSION.USER_ID)= '" & strUserName & "' ) AND " _
& " ((SUBMISSION.DATE) Is Null) )", _
conBreakdown, adOpenStatic, adLockReadOnly, adCmdText
If Not rsSystemChkTotal.BOF Or Not rsSystemChkTotal.EOF Then
lblDTotal.Caption = Format(rsSystemChkTotal!SumOfSystemChk, "#,###,##0.00")
rsSystemChkTotal.Close
Else
lblDTotal.Caption = Format(0, "#,###,##0.00")
lblCount.Caption = "Total Checks: " & 0
rsSystemChkTotal.Close
End If
'Display Existing Check amounts total
If rsTotal.State = adStateOpen Then rsTotal.Close
rsTotal.Open " SELECT Sum([Amount]) AS TotalCAmt FROM tbl_CheckAmounts " _
& " WHERE User_ID = '" & strUserName & "' ", _
conBreakdown, adOpenStatic, adLockReadOnly, adCmdText
If rsTotal.RecordCount > 0 Then
If IsNull(rsTotal!TotalCAmt) Then
cTotalChkAmt = 0
Else
cTotalChkAmt = rsTotal!TotalCAmt
End If
lblCTotal.Caption = Format(cTotalChkAmt, "#,###,##0.00")
rsTotal.Close
End If
End Sub
Private Sub grdChkAmts_AfterInsert()
conBreakdown.Execute "UPDATE tbl_CheckAmounts SET tbl_CheckAmounts.User_ID = '" & strUserName & "' " _
& " WHERE tbl_CheckAmounts.Amount = " & cAmt _
& " AND ((tbl_CheckAmounts.User_ID) Is Null)"
If rsTotal.State = adStateOpen Then rsTotal.Close
rsTotal.Open " SELECT Sum([Amount]) AS TotalCAmt FROM tbl_CheckAmounts " _
& " WHERE tbl_CheckAmounts.User_ID = '" & strUserName & "' ", _
conBreakdown, adOpenDynamic, adLockReadOnly, adCmdText
If rsTotal.RecordCount > 0 Then
If IsNull(rsTotal!TotalCAmt) Then
cTotalChkAmt = 0
Else
cTotalChkAmt = rsTotal!TotalCAmt
End If
lblCTotal.Caption = Format(cTotalChkAmt, "#,###,##0.00")
rsTotal.Close
End If
End Sub
Private Sub grdChkAmts_KeyDown(KeyCode As Integer, Shift As Integer)
Const VK_TAB = &H9 ' TAB
Const VK_ENTER = &HD ' ENTER
If ((KeyCode = VK_TAB) Or (KeyCode = VK_ENTER)) Then
cAmt = grdChkAmts.Text 'cAmt Form level
KeyCode = vbKeyReturn
SendKeys "{DOWN}"
End If
End Sub
Private Sub grdChkAmts_KeyPress(KeyAscii As Integer)
If Not ((KeyAscii = 8) Or (KeyAscii = 46) Or KeyAscii = 44 Or (KeyAscii >= 48 And KeyAscii <= 57) Or (KeyAscii = 13) Or (KeyAscii = 9)) Then
KeyAscii = 0 'Allowed only Backspace,.,,,0 to 9,enter,tab
End If
End Sub
Private Sub grdChkAmts_Click()
grdChkAmts.ClearSelCols
End Sub
Private Sub grdChkAmts_ColResize(ByVal ColIndex As Integer, Cancel As Integer)
Cancel = True
End Sub
Public Sub GridResize()
Set grdChkAmts.Columns("Amount").DataFormat = sfmt
grdChkAmts.Columns("Amount").Width = 3500
grdChkAmts.Columns("Amount").Alignment = dbgRight
End Sub
How to update data in between? there is no key value and multiple rows(and for other users) will have same check amounts.....
Need Help!!!!!!!
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Edited by - sreddy on 1/10/2008 9:23:16 AM
HELP: INSERT DATA INTO TABLE!!
OKay, I get a error msg saying that one of my fields cannot be a zero-lenghted string. i.e ""
How can I get round this problem? Is there anyway to check the fields properities and see if it is allowszerolength data?
I belive I am using ADO and maybe DAO
HELP PLEASE!!
----------- CODE TO INSERT DATA TO TABLE ---------------
With dbdata.rstmpCardHolderDet
.AddNew
For i = 0 To UBound(mField) - 1
Debug.Print i; .Fields(i).Name
'if the fieldtype is TEXT
If .Fields(i).Type = Adodb.adVarWChar Then
.Fields(i).Value = RTrim(mField(i))
Debug.Print .Fields(i).Value
'if the fieldtype is DATE
ElseIf .Fields(i).Type = Adodb.adDate Then
.Fields(i).Value = CDate(RTrim(mField(i)))
Debug.Print .Fields(i).Value
'if the fieldtype is LONG BINARY
ElseIf .Fields(i).Type = Adodb.adLongVarBinary Then
.Fields(i).Value = mField(i)
Debug.Print .Fields(i).Value
'if the fieldtype is LONG TEXT
ElseIf .Fields(i).Type = Adodb.adLongVarWChar Then
.Fields(i).Value = RTrim(mField(i))
Debug.Print .Fields(i).Value
'if the fieldtype is anything else turn it to integer
Else
.Fields(i).Value = Val(RTrim(mField(i)))
Debug.Print .Fields(i).Value
End If
Next
.Update
End With
|
TRACKER
