Problem with tickbox...again

[k983111]

Can someone please help me with my code, I’m a bit confused. The code below is in the After_update of the tickbox control and On_current of the form. It works fine until another dropdown box has a value selected on the same form. This has nothing to do with the code below, so I don’t know why it effects it. The DateToStock.Default Value is where the problem is occuring.
Thank you in advance.



Private Sub Form_Current()

If Me.tickbox = True Then
With Me
.consignee.Enabled = False
.DateToStock.Enabled = True
.DateToStock.Visible = True
.DateToStock.DefaultValue = "Date()"
.dts.Visible = True ' label
.dtc.Visible = False ' label
.DateToConsignee.Enabled = False
.DateToConsignee.Visible = False
.DateToConsignee.DefaultValue = "Is Null"
End With
Else
With Me
.consignee.Enabled = True
.DateToStock.Enabled = False
.DateToStock.Visible = False
.DateToStock.DefaultValue = "Is Null"
.dts.Visible = False ' label
.dtc.Visible = True ' label
.DateToConsignee.Enabled = True
.DateToConsignee.Visible = True
.DateToConsignee.DefaultValue = "Date()"
End With
End If
End Sub

 

[pdx_man]

Remove the quotes on "Date()"

 

[k983111]

Thanks for the reply. I tried removing the " " from date and the () then disapeared. It also stopped the date working.

 

[pdx_man]

Try:

"#" & Date & "#"

 

[Drevlin]

I'm curious if the above solution fixed your problem?
It seems to me the original way was correct. Since the code is actually filling in the property Field of the textbox (which in this case only excepts a string) you would need to put quotes around the Date().

Another way of thinking about it would be if you tried to have the default value of a textbox be the word: test. In the property dialog for defaultvalue you would actually put: "test". Access usually places the quotes around what it perceives to be a string automatically so you can type it without the quotes, but if you deleted the quotes you would end up with a #name? Error.

So if you wanted to use VBA to change the default value to 'test', you would have to do the following:

Text26.DefaultValue = "'test'"

The solution of: "#" & date & "#"

Basically does the same thing. Which is why I'm curious if it actually fixed the problem.

Also, as far as the code itself is concerned, the "With Me" line seems to be unnecessary.
In a form's Module "Me." is implied. Therefore when you reference a form's textbox Text01 it is unnecessary to type: Me.Text01

And since everything inside the "With... End With"s seems to be changing properties of controls the only thing it would do is to slow down your code. It might even be the problem with your form (though I seriously doubt it).

If your problem is't fixed yet, could you explain what type of error your actually receiving?

 

[pdx_man]

Agreed. The code that you had initially should have worked.
Removing the quotes only wouldn't work because then it looks at the date as an expression:
09/25/02 = nine divided by 25 divided by 2 = .18
Using the # tells Access, "Hey, I want this to be a Date"


As far as the With ... statement, it is always a good idea to detail the location of the controls. It saves any confusion as to if it is a variable or a control, especially if the programmer is not using proper naming conventions. At least in my experience. You are not going to take a performance hit, either.

 

[Drevlin]

... each exclamation point and dot represents execution time spent processing the reference... Each point or dot that you eliminate results in faster code. For a single procedure, the number of points and dots may not matter much, but think of the number of points and dots there can be in a fully automated application. So, the name of the game is to count and minimize the dots.
"Access 2002 VBA Handbook" by Susann Novalis and Dana Jones
Sybex Inc. - 2001

The above quote is the reason for my statement about slowing the code down. Not, that I think everyone should believe everything they read... but it seems logical enough to me to follow it as a rule.

Peace

 

[pdx_man]

Well, I'm not going to argue with the quote, but do your own analysis. If you are looking to save 3 nano-seconds, then by all means, elimanate. If you are firing off a procedure that iterates 1000's of times, you may save 1 second. But the programmer that will be maintaining the database after you may appreciate better qualified references to the controls. There is always a balance between performance and ease of understanding the flow of code. When do you pull out a segment of code to its own procedure? When do you create a class object? These are not necessarily hard-and-fast rules. So, do your own analysis, see what you are getting for what you are giving up.

 

[Drevlin]

I completely understand. Wasn't trying to start a war, I only wanted to give the reason behind my previous statement. Just to prove there was one, not to prove it was right.

Peace

 

[pdx_man]

Sure, no prob, no war. As I mentioned, there are instances when you need to find every aspect where a code can be optimized. Chances are, though, it's not going to be in an Access form/report

 

[k983111]

Thanks for the attention guys. I tried the last code, but with no success. This is my college project and i dont think i'm going to get it to work. Iv'e included a copy incase its something else iv'e done wrong.
If you go to the scansheet form and click the tickbox on and off and view the results with the date fields, then try it again after choosing an item from the consigner drop down, it stops working?.

 

[k983111]

Thanks for the attention guys. I tried the last code, but with no success. This is my college project and i dont think i'm going to get it to work. Iv'e included a copy incase its something else iv'e done wrong.
If you go to the scansheet form and click the tickbox on and off and view the results with the date fields, then try it again after choosing an item from the consigner drop down, it stops working?. pls rename file to .rar

 

[pdx_man]

Then this is not where your issue lies.

I was unable to unzip this file. Please re-zip and try again.

 

[Drevlin]

When you say it stops working... do you mean the Date to Stock Textbox stops showing a date? Or is there something besides that?

 

[Drevlin]

Ok. The first thing I did was to clean up your code. You have at least four proceedures that do the exact same thing.
My solution?
Create a single proceedure called:
MakeChanges() or DoStuff()
or whatever you want to call it.

Then when you want a proceedure to do the changes you just have:

Private Sub Consignee_AfterUpdate()
DoStuff
End Sub

This will make your code a lot simpler to read because there will be a lot less to read.

Anyway in order to fix the problem itself...
Instead of just this:

.DateToConsignee.DefaultValue = "Is Null"
...
.DateToConsignee.DefaultValue = "Date()"

Do this:
.DateToConsignee.DefaultValue = "Is Null"
.DateToConsignee = ""
...
.DateToConsignee.DefaultValue = "Date()"
.DateToConsignee = Date

Just changing the default value doesn't necessarily change what is in the textbox. In order to be safe, change both.

I think that should take care of the problem of the date disappearing. If I'm off on what the problem is let me know.

Peace

 

[k983111]

Solved it

I could not send the file as a zip as it was too big, so i used winrar to compress it and changed the extension to .zip change the file back to .rar and it should open.
Thanks for the advice Drevlin thats cracked it.
Thanks to everyone who's helped. Great site.

 

[k983111]

Spoke too soon

That solution fixed the disapearing date problem, however... now the DateToStock gets entered when it shouldn't i.e. When the default value should be null.

 

[Drevlin]

Try putting the:

DateToStock.DefaultValue = "Is Null"
DateToStock = ""

Before:

DateToStock.Enabled = False
DateToStock.Visible = False

I believe the way you have it now changes the value after you've disabled the textbox, which means the changes aren't being sent to your table.

 

[k983111]

The date to stock has now disapeared and im starting to go cross eyed.

 

[Drevlin]

Ok... maybe you should explain exactly what you want this form to do and explain what it isn't doing correctly. I don't seem to be picking up on where your problems lie...

I'll give you what I gather and then you can fill in the blanks/mis-conceptions.

This form that your working on is meant only for adding new stock/consignments. The new item can either be consignment or stock. Therefore the person enterring the information checks the box if it's to be placed into stock. And since stock doesn't have a consignee you don't want a consignee enterred into the table or a consignee date. On the other side of the coin, if it is a consignment you want it to have a datetoConsignee and a consignee name but not date to stock.

Is this the gist of your situation?

And if it is let me know what isn't working exactly.