Replies

Yes, otherwise your first suggestion would have worked fine. Except for the column header all cells must be populated with a COST_CENTER number.

Other cells in column A are populated with other cities and also the months. This data is irrelevant as well.

I have plucked the ctrl and D keys from my keyboard.

I want to fill Column A to capture all the data related to it in the other columns.

Gentleman, thank you for your response. When I do this manually I select a range and use ctrl D and it copies over the occasional non-blank cell that does not have a number. (Something I failed to mention in my post)

I have attached a sample.

Gentlemen, thanks for your input.

Although the database is shared, it is not split. I have no say over it, I just get a call to compact it now and then. Each user is in the habit of opening it and leaving it open all day. I knew Access would know if more than one user had it open, but I did not know if Access could prevent it being opened while being compacted. I take it, it cannot.

Works beautifully! Thanks so much.

I have a calculated field on the sub form and when it reaches 80 I want the forms allow edits property to be set to False, and when a new record is started and the value is less than 80 to be set to True. I set the sub forms allow edits property to false and used the following code:
If Me.Hours2 80 Then
Me.AllowEdits = True
End If

Problem is I can’t get it to work. I have attached a database.

Once again, I thank you for your help. This forum is something special to me. It makes me feel I have friends all over the world.

When I use this code I get a “variable not defined” error at “adhcErrInvalidRow”.

I decided to work around by setting the BorderStyle property to None and using a rectangle to surround the form. That allows the choice of a color.

Thanks Hans.

I see the error of my ways. I had combined the Parces ID and Tax Authority ID as a single key. Thanks for your help.

This works perfectly. Obviously I could not have even begun to do this without you. Many thanks.

Edited by HansV to provide link to post – see Help 19

Thanks for the suggestion Steve…I definitely should have known that.

Hans, I refer you back to a previous post you helped me with, post 574114 my function that doesn’t do anything. Well this is another step I need to add to give me something to reconcile too.

I import the data from a large number of spreadsheets and process it through a database. The last time I ran my process there were a few sheets that were not picked up. I ran them by themselves and they processed perfectly. I thought if I could count all the rows in each spreadsheet and put them into a table, I would then have a total record count to reconcile to against my Access table where all the records are imported. Then I could know if something failed.

The first column of each spreadsheet is what I will use. I created a macro in Excel to do the count, but I do not know how to put it into VBA in Access, nor do I know how to store the data from each count into a table. So all I have is the macro below.

Sub RowCount()
ActiveCell.FormulaR1C1 = “=COUNTA(R[-3]C[-9]:R[996]C[-9])”
Range(“J5”).Select
End Sub

It works perfectly! For the sake of explanation, the spreadsheets were originally delivered to the users as text files (don’t ask me, I have no idea why), and some of the users saved them that way after they added their data. Others however, saved them as xls. To make them consistent I added this function to my procedure prior to importing the data.

Thanks again for your help and patience.

When I step through the code and hover over xlWsh in the IF statement it equals nothing and never changes. Shouldn’t it change to =Test?