Replies

I’d aim towards drastically simplifying the main form, perhaps by replacing it with a series of linked forms. I’d create those from scratch, instead of copying bits and pieces from the problem form, to avoid copying the corruption, wherever it may lurk.

Thanks Hans. There is a lot of code in the forms. Could I copy it to a text file or a VBA storage program and then copy it back out?

Many things can effect bigger forms.

Compile often if vba code is one I was advised.

Another is to turn off name auto correct which has always been flakey

see http://allenbrowne.com/bug-03.html

If you have a lot of code try a decompile every now and again

Thanks, Andrew. I know Allen’s site and have run decompiles more than once. I always have name auto correct off.

I think there is a upper limit on how many times you can edit and add controls to a form which I’ve seen documented somewhere but don’t remember where. One trick you might try is saving the database to text and then loading it from text. I have seen that fix some corruption problems with forms.

Can you explain saving the database to text, Wendell?

This form has definitely been edited a lot. My guess is that it has reached the end of its useful life.

Just a thought, this can also be caused by another total in a footer being wrong.
I have noticed in Access that if there are a couple of totals and one of them yields an error, that will often all yield an error.

Hi Andrew:

Thanks for the suggestion. This was actually what the problem was. My second totally column was WkAmt * 52 and I was just summing this calculated amount. Access didn’t like that. Once I changed the formula to calculate in the total box =Sum([WkAmt]*52) then the errors went away.

Just a thought, this can also be caused by another total in a footer being wrong.
I have noticed in Access that if there are a couple of totals and one of them yields an error, that will often all yield an error.

I’ll check out this possibility when I am back at the office. Thanks for the suggestion.

Is the name of the control on the subform bound to the WkAmt field also WkAmt?
If not, is there another control named WkAmt?

One other thing: have you checked whether the values of WkAmt in the source table are still OK?

Thanks for this. There is a possibility that this might be the problem. I was doing some copying and pasting and other similar stuff and I might have ended up with two controls named WkAmt. I can say with certainty that the values in WkAmt are OK in the source table since I was just creating this form today and there are only 7 test values which I added while I was working. Is there any possibility that other calculated fields on the form detail level could be messing up the subtotal? There is another control that takes the value of WkAmt and multiplies it times 52 for a Yearly amount (wk being Week).

Regardless this gives me some food for thought and I will pursue it when I am back in the office on Tuesday.

Your solution is probably correct. I used the following and I got the 0s:

Vision Revenue Cross Sold: IIf([Vision Status]=”Gain” And [Product Category]=”Two or More products” And ([rcac ucci in]=”Y” Or [JanHMIG]=”Y”),[Vision Projected Revenue],0)

At least I hope it is correct. Is the way I did AND with the two OR fields correct? Vision Status = gain and Product category = Two or more products and then either one of the rcac ucci in = Y or JanHMIG = Y then I want vision projected Revenue else a 0.

On the face of it, it looks correct to me. I always consider that if the formula is giving me the answer I want/need then it is correct. In the previous one, you were definitely missing the third part of the outer statement. This one has three parts and your Ors are in brackets, so it should work.

Where in this query calculation would I put NZ? I keep getting incorrect number of arguments. I should get 0’s but I am getting blanks so I thought I needed NZ.

Dental Members Cross Sold: IIf([Dental Status]=”Gain”,IIf([Product Category]=”Two or More products”,[JanDentalMem],0))

You have nested IIf statements so both the inner bit and the outer bit need three parts. You are missing the third part of the outer bit. The last part should like this: [JanDentalMem],0),0)

Which will give you a 0 if Dental Status is not “Gain”. You will also get a 0 if Dental Status is “Gain” but Product Category is not “Two or More Products”.

Hopefully I did this right! Hans will correct me if I didn’t….

I finally had a chance to look at this tonight and it is all working properly once I got the dots in place. Many thanks.

The form opens with deletions allowed for me. I don’t know why.

I found the problem, too stupidly obvious to go looking there in the first place. The Immigration Status subform had Allow Deletions set to Yes in the properties window. Once I changed that, the form was locked or unlocked according to permissions in the VBA.

Thanks for looking at it anyways.

Hello,
I’ve created an access database (2003), & I went into tools-> startup, and like a goof, I toggled off the “allow full menus” option & the submenus option below it….. I also chose to have one of my forms open up upon startup.

Now, I can’t access any of the menus. When I open the database, the form displays & only the menu related to the form displays. I don’t have the normal file, windows, tools, help menu’s anymore. Does any one know how to get them back?!

This is what I get for playing where I shouldn’t be!!!

Thank you!!!!

Hold down the shift key on startup and you will have full menus.

I have an access 2003 app running on a machine not connected to the Internet, so it doesn’t have ll the latest Access updates. However, I don’t need the updates. Upon startup I get several dialog boxes about possible unsafe code. There’s nothing unsafe and I need to get rid of the dialog boxes. Any suggestions?

Go to Tools menu then Macro/Security. Set it to low and the dialogues will go away. I would strongly encourage you to get the updates. They fix a lot of bugs.

Private Sub Check_In_Click()
If Me.Dirty Then
If IsNull(Me.CompanyOrDepartment) Then
Me.CompanyOrDepartment.SetFocus
MsgBox “Company Name is required. Enter ‘None’ if no company name is available”, vbExclamation
Cancel = True
Exit Sub
End If
RunCommand acCmdSaveRecord
End If

Try Docmd.Runcommand acCmdSaveRecord

Hello.
When linking to a SharePoint Server, is it possible to set a refresh interval?
Thank you, Andy.

Since no one else has jumped in, I have had one very brief experience with SharePoint a few months ago. My impression was that if the database is simple, it will work fine. Certainly exporting to SharePoint lists(? I forget the term) was pretty simple.

In terms of refreshing, if you are using Access forms to interface with the data, you should be able to just use the timer event to run a sub that refreshes the data.

If you are doing intensive updating of data, I found my database which worked very quickly with an Access backend ran excruciatingly slowly with the data in Sharepoint lists. 10, 15, 20 seconds to update data in a form. I recommended to them that they really needed to move to a SQL Server type of environment where the heavy lifting is done on the server side rather than the front end like Access does.

If I have a table that contains records identifying a field name, field type, default value, format, caption etc., can I use the records within the table to create a table from scratch? Would I use DAO or ADO?

Is it also possible to create export specifications from a table? For example if I have a table with all the field names, size, starting position, type, can I create a text Export spec? I have several tables that I need to export to fixed width text file, and I also have the specifications for the layout in a separate table. I am looking for a way to create the spec from that table, instead of manually creating the spec.

Thanks for your consideration.

Ken

Play around with Export from the File menu. This only allows you to do one at a time but gives you all standard formats, excel, text, csv, other database formats. You can use the records within a table to make another one by using a Make Table query. If you want to do more than one table at a time, you would have to use code. A lot depends on what it is you need to do with these tables in terms of determining the most efficient way to do it.