Refreshing a Datagrid

[lee123]

I know this is a free site and the moderators or anybody don't have to answer if they don't want to.. but if there is someone who knows how to answer this question please help with this one.

i have changed my project so many times to try to get the results i want but can't seem to get this one thing to work the datagrid and datalist to work right. i have looked on the web to see if i could do it another way. and read so many books but there isn't another way i have found i have searched this site but nothing but the posts i have left and certainly i won't find the answers there. Because it only has the same question posted. so i guess i will go through my question again.

I have made a program so when i pay a bill i can click on the name of the bill in the datalist. and only them bills for that name will show in the datagrid.

the way i have this set up is, i have TWO ADODC controls on the form. and one datagrid and one datalist.

the first ADODC control is for the DATALIST and the second ADODC control is for the DATAGRID.

the datagrid ADODC2 is connected to a database i have made with the "Visual Data Manager" called "BillDetails" the connectionstring is connected to "BillDetails" and the recordsource is to the table "DetailsT" which in the database i have these fields

BillID
AmountDue
AmountPaid
DueDate
DatePaid
ReferenceNumber
BillBalance

The "BillID" I have made a text field because i want to add my own ID's. I don't know if you can do this but i have.

the datalist ADODC1 is connected to a database i have made with the "Visual Data Manager" called "BillInformation" the connectionstring is connected to "BillInformation" and the recordsource is "SELECT * FROM DetailsT" the "ROWSOURCE" is to the ADODC1. the "LISTFIELD" is set to the "BILLNAME" the "DATASOURCE" is to ADODC1 the "DATAFIELD" is to "BILLNAME" the "BOUNDCOLUMN" is to the "BILLID" which in the database i have these fields

BillID
BillName
Address
City
State
ZipCode
PhoneNumber

The code for the DATALIST click event is this:

Private Sub Datalist1_Click()
Dim strQuery As String
strQuery = "Select * FROM DetailsT WHERE BillID = " & _
DataList1.BoundText & "'"
With Adodc2
.RecordSource = strQuery
.Refresh   '<-----------'This is where i am getting the error
                          'it's not refreshing the datagrid
End With
With DataGrid1
.ClearFields
.ReBind
End With
End Sub

So there it is i hope i can get a response for this

lee123

[QVeen72]

Hi,

You have one extra single quote in your query..

In the Table if BillID is Numeric, then give your Query statement like this :

strQuery = "Select * FROM DetailsT WHERE BillID = " & _
Val(DataList1.BoundText)

If it is Text then :

strQuery = "Select * FROM DetailsT WHERE BillID = '" & _
DataList1.BoundText & "'"


Regards
Veena

[lee123]

Well I have Tried Both Ways And It Still Points To The "REFRESH" the error it gives me is (Syntax error in FROM clause)

lee123

[lee123]

thanks for the response it took me awhile to figure it out but i got it working i have another question for you after i have click on a name in my list and the datagrid changes to that bill there are currency values in the the details database how do i show them in currency in the datagrid after i have click on a name. because right now when the form opens every bill is showing and i have set the currency value through the properties in the datagrid and it is currency.

but when i click on a name it goes back to normal. how do i change that

lee123

[lee123]

i have got this working but i can't print out the datagrid in a txtfile so i would like to know how i can use the datacombo and textboxes for this instead, I mean is the code any different than the datagrid because i have 8 textboxes i would like to bind to it. so when i drop down the datacombo box it pulls the information that goes with the name..and fills the textboxes with the details

lee123

[lee123]

please respond to this question.............

lee123

[QVeen72]

Hi,

Add Place the Textboxes on the Form, and Bind them to the ADODC,
by setting "DataSource" and "DataMember" properties of the textbox..

Regards
Veena

[lee123]

ok i have the textbox already on ther form i have got the one adodc control bound to the datacombo (Information),

then i have Eight textboxes on the form as well for the (details) with another adodc control if i understand what your saying is i don't need the other adodc control?

Do I use both Adodc Controls for this? or just the one for the datacombo for all?

lee123

[QVeen72]

Hi,

I Guess, you are doing a Master-Detail format, then you will need 2 ADODC's.
You can Bind a Textbox (Or any) Control to Only one ADODC.
Check which adodc has the fields you are wanting to display, and bind that.

Regards
Veena

[lee123]

well the thing is that i have these textboxes on the form but i want the company name from another table so when i choose a name the information will fill in the textboxes is this possible through code, through the one adodc control to get the name from the other table?

lee123

[QVeen72]

Hi,

Yes, If the data is from another Table, then you will need another ADODC

Regards
Veena

[lee123]

all of that being said how do i modify this code to do this with 8 textboxes instead of a datagrid:

Dim strQuery As String
strQuery = "Select * FROM DetailsT WHERE BillID = """ & DataList1.BoundText & """"
With Adodc2
    .RecordSource = strQuery
    .Refresh
End With
With DataGrid1
    .ClearFields
    .ReBind
End With

lee123

[QVeen72]

Hi,

For each TextBox, write this code in FormLoad event:

Set Text1.DataSource = ADODC1
Text1.DataField = "Field1"

Change control/Field names accordingly..

Regards
Veena

[lee123]

For each TextBox, write this code in FormLoad event:

Set Text1.DataSource = ADODC1
Text1.DataField = "Field1"

Change control/Field names accordingly..

OK i have done what you have said and the only one that works is the "ID" field the rest keeps giving me an error please view the code and see if i have done this right:

Private Sub Form_Load()
    Set BillID.DataSource = Adodc1
    Set AmountDue.DataSource = Adodc1
    Set AmountPaid.DataSource = Adodc1
    Set DueDate.DataSource = Adodc1
    Set DatePaid.DataSource = Adodc1
    Set PaymentType.DataSource = Adodc1
    Set ReferenceNumber.DataSource = Adodc1
    Set BillBalance.DataSource = Adodc1
    BillID.DataField = "BillID"
    AmountDue.DataField = "AmountDue"
    AmountPaid.DataField = "AmountPaid"
    DueDate.DataField = "DueDate"
    DatePaid.DataField = "DatePaid"
    PaymentType.DataField = "PaymentType"
    ReferenceNumber.DataField = "ReferenceNumber"
    BillBalance.DataField = "BillBalance"
End Sub

just one more question. in the properties box of each textbox do i keep them bound to the other adodc or not and there field names.

lee123

[QVeen72]

Hi,

What is the Recordsource of ADODC1..?
All the FieldNames should be present in that recordsource..

Regards
Veena

[lee123]

it is to a table called "BillInfo".... Oh Maybe the "BillID" works because That Is The One Field That Is The Same In That Table. you see that's why i asked you how can i, or is it possible to do this in code with two tables in my first post i list all the fields on both tables

ADODC1

BillID
BillName
Address
City
State
ZipCode
PhoneNumber 

ADODC2

BillID
AmountDue
AmountPaid
DueDate
DatePaid
ReferenceNumber
BillBalance

you know i thought that the datacombo was like the datalist you could use this same code:

Dim strQuery As String 
strQuery = "Select * FROM DetailsT WHERE BillID = """ & DataList1.BoundText & """" 
With Adodc2 
    .RecordSource = strQuery 
    .Refresh 
End With 
With DataGrid1 
    .ClearFields 
    .ReBind 
End With 

i guess you can't modify this code for the datacombo to work

lee123

[QVeen72]

Hi,

In that Case, Set TextBox's DataSource To ADODC2.
Since BillID is common in both the tables, it is being shown..
For others, just change the datasource..

Regards
Veena

[lee123]

well i tried what you have said to do but it still don't work, i have changed all to adodc2 in code and got an error then i tried to change it in the properties box but that didn't work either. these are separate tables i am using i know the id's are the same but they have different fields that's why it probably don't work. but thanks anyway for your responses i appriciate it very much..

lee123