Replies

See VBA Tips & Tricks: Create Database with ADO / ADO Create Database for code how to create your table.
You’ll have to create the table and each field. Access can’t assume anything.

Can’t you do a DCount on the table or query with the same where condition as in the sql statement ?
Something like this :

NumberOfRecords = DCount(“*”,”MyTableOrQuery”,”WhereStatement”)
If NumberOfRecords > 0 Then
DoCmd.RunSQL strSQL
End If

Have a look at the procedure at Export data from Excel to Access (ADO) using VBA in Microsoft Excel

According to this thread Error communicating with the OLE server or ActiveX Control in Forms Coding in a Microsoft discussion group, several people solve this problem by copying the form, delete the form and renaming the copy to the old name.

As usual, try this on a copy of your database.

HTH

Vicky,

Have a look at this post from Hans. You should do something similar.

Nancy,

Please post in English so that everyone can follow the thread.

I found two problems in your database:
– the subform contains a subset of the whole table.
– the subform is sorted descending

I modify the function ChangeColor and the query QrysubLogboek.

Can you test the modification in your db ?

Are you saying that the conditional formating is not working or that the AlternateColor field is not correct ?
Maybe you can attach a stripped down version of your DB, so we can see what you have.

No, you can’t use conditional formating to change the backcolor of the detail section.
What you can do is create a textbox as great as the detail section, put it behind all the other textboxes and change the backcolor of that textbox with conditional formating.

You have to set the conditional formating for each field on the form individually.

You have to click on the little arrow (see attachment) beside the paint pot to change the color.

Nancy,

I have been searching a little and I think I have to change my mind. Look at the attached mdb and see if you can do something with it.
I wrote a function to add a Boolean field to the recordset. That field change from true to false and vice versa each time the date change in the date field. Once that field is created, it’s easy to use the conditional formating to change the backcolor of each field in the form.

HTH

Hi Nancy,

I think this is not possible in a continuous form or in datasheet view. With some VBA, you could achieve this in a report.

Are you sure about the query names ?
In your first post you mention qryOrigRUGItems and now qryEditRUGitems

Would’nt it be something like this :

UPDATE cr_SampleDataSet_HeaderInfo SET LoginID = ”, NetworkLogin= ”, ReturnedFromField=’Yes’ WHERE someIDfield IN (Select someIDfield from qryOrigRUGItems)

Open the form in design view. Select the form. In the property window, look for the property “Filter On Load” in the tab Data. If it is set to yes, change it to No. Save the form.

Add thes two lines to the code :
strDealer = “”
strDataID = “”

Code:

...
  Do While Not rs.EOF
    strUsername = rs!User
    strDealer = ""
    strDataID = "" 
    Do Until strUsername  rs!User
       strDealer = strDealer & "|" & rs!Dealer
...