[RESOLVED] VB6 to MS Access 07 - ADO / datagrid.

[rainer]

Hello everyone,

I am using an ADOdc object to connect to an MS Access 07 database from my VB6 program.

In my first form, I add some data to a table in the database. In my second form, I then use an SQL query to search for the previously entered primary key (from the previous form), and only display records with that primary key in the datagrid.

Unfortunately, the datagrid will not display the last created record (i.e. the most recent record, which was added in the previous form).

The SQL query searched by a defined parameter "OrderID", which is taken from a text box on the form. If i set the text box to a number before compiling, instead of calling it from the previous form, the datagrid will display the correct record. Also, if i set the SQL query to display all records, it still will not show the most recent record.

As I say in the code, the SQL query works just fine if i set the text box to a number of an existing record before i run the code, so I believe the problem is just to do with the most recent record for some reason.

Sorry it's a long post, and any help would be massively appreciated. If it makes it easer for you to see the project itself, please ask.

Thanks again, best wishes,

[si_the_geek]

Welcome to VBForums

I think you will find the answer in the "Classic VB - Data-bound controls" section of our Database Development FAQs/Tutorials (at the top of this forum).

There are only two articles in that section, the second of which has the answer, and the first (which I would recommend reading) explains why there aren't more articles in that section.

[rainer]

Hello Si,

Thanks very much for the information.

Below my SQL query I added the ...recordset.requery command, but unfortunately I am still having the same issue.

Again, the SQL works with an existing OrderID.

New code:

Me.adoOrderBunch.RecordSource = (SQL query)

Me.adoOrderBunch.Recordset.Requery
Me.adoOrderBunch.Refresh
Me.datOrderGrid.Refresh

Any more ideas?

Thanks again friend,

Much appreciated!

[si_the_geek]

OK, well in that case are you sure the record has actually been added to the database before this query runs?

To check, open the table in Access when the code reaches that point.

[rainer]

Hello there,

Yep I have opened the database after the previous form information is submitted and yes, the new record is there in the database.

It's strange, the problem is only with the most recent record. If i set the Sql query to OrderID - 1 it will work for the record before.

I have also tried using "Rebind" on the datagrid as opposed to the refresh method.

Any more ideas?

Thanks very much for your time Si, it's very helpful!

[dee-u]

Have you read and tried the suggestions in this FAQ article?

[rainer]

Hello there Dee,

Yeah I read your comment and the others in that thread. I changed the code to rebind and still no success.

This one's really bugging me now

Thanks for your help so far guys,

Any advice welcomed!

[dee-u]

Could you try it like this?

Me.adoOrderBunch.RecordSource = (SQL query)
Me.adoOrderBunch.Recordset.Requery
Me.adoOrderBunch.Refresh

Set datOrderGrid.DataSource = adoOrderBunch
Me.datOrderGrid.Refresh

[rainer]

Hello Dee,

With that combination I get "Object variable or with block variable not set" error at the "recordset.requery" line.

If I put the refresh before the requery, no error but still the record is not displayed.

Strange :S

Thanks man, anything else you/anyone think I could try?

[dee-u]

How about this?

Me.adoOrderBunch.RecordSource = (SQL query)
Me.adoOrderBunch.Refresh
Me.adoOrderBunch.Recordset.Requery

Set datOrderGrid.DataSource = adoOrderBunch
Me.datOrderGrid.Refresh
Me.datOrderGrid.ReBind

[rainer]

Same old chestnut, nothing displayed in my grid

[dee-u]

Could you post your project and tell us how we could replicate what you are doing?

[rainer]

Okay then. Unzip Project.zip, open Bloom.vbp, and set startup to frmAddOrderCustomer if it is not already.

Run the project, and first select a customer from the combo box (any customer is fine). In the second window, select a bunch and a quantity (there is no validation so ensure the quantity is numeric. Press add to order, and the form with the datagrid will appear, showing no results.

The database is included so you can see the relationships etc.

If you have any questions about any of it please ask.

There are other forms in the project but these are just to add customers, bunches, orders etc. They are not relevant to this code. I am also yet to comment the code so if you cant work it out just ask.

Thanks a lot guys, you've been very helpful.

[dee-u]

The Load event of your frmAddOrder is already triggered when you are showing frmAddOrderCustomer so when you are showing it in your cmdAddItem_Click in frmAddOrderItem then its not being refreshed since the load event is not being called.

One quick solution is to add this in your frmAddOrder

Public Sub RefreshRecord()
    Me.adoOrderBunch.RecordSource = "SELECT Bunch.[Bunch Name], Bunch.[Bunch Unit Price], OrderBunch.Quantity, [OrderBunch]![Quantity]*[Bunch]![Bunch Unit Price] AS SubTotal FROM Bunch INNER JOIN OrderBunch ON Bunch.[Bunch ID] = OrderBunch.[Bunch ID] where OrderBunch.[Order ID] like '" & OrderID & "%';"
    Me.adoOrderBunch.Refresh
    Me.adoOrderBunch.Recordset.Requery
    
    Set datOrderGrid.DataSource = adoOrderBunch
    Me.datOrderGrid.Refresh
    Me.datOrderGrid.ReBind
End Sub

then call it like

Private Sub cmdAddItem_Click()

Me.adoBunchORder.Refresh
Me.adoBunchORder.Recordset.AddNew
Me.adoBunchORder.Recordset.Fields("Quantity") = Me.txtQuantity.Text
Me.adoBunchORder.Recordset.Fields("Bunch ID") = Me.adoBunch.Recordset.Fields("Bunch ID")
Me.adoBunchORder.Recordset.Fields("Order ID") = Me.txtOrderID.Text
Me.adoBunchORder.Recordset.Update
Me.adoBunchORder.Refresh

Me.adoBunch.CommandType = adCmdTable
Me.adoBunch.RecordSource = Bunch

'Me.adoBunchORder.Recordset.Close
'Me.adoBunch.Recordset.Close

Unload Me
frmAddOrder.Show
frmAddOrder.RefreshRecord

End Sub

[rainer]

Wow, Dee, that's worked a charm. I realise now that I called the form when sending the OrderID in the text box, oops!

You've been very helpful, so thanks very much. It's much appreciated man!

Best wishes,

Rainer

[dee-u]

No problem. Glad to be of help. You can now mark your thread as resolved.