Noob VBA help

1: Got 'Date Control' but would prefer Calendar control, found it now and its up, it works for selecting a date for a new entry, but it wont select a day of work from the calendar, can i not do that?

2: Where i've got Pay - PAYE i need to save that to my table field 'pay' when all the other fields save but currently it has the equation for - tax in the Control Properties, any ideas on how i can transfer it to my table?

I was thinking along the lines of a hidden textbox which contains a mirror value of the pay result which then is saved when pressing the save button (like all other fields) but to mirror it needs the control property entry, had a bash at = [pay*] & (or AND) = [Calcpay**] but that doesn't work :D

*being my field name in table
** being my textbox name

Many thanks.

PS: I cannot upload an image yet due to post restrictions but its here:


http://img361.imageshack.us/img361/5776/vba2jg9.png


It a mod could stick in the http bit and allow for my 1st post to make sense that would be ace!

Cheers.

Welcome to the forum- always good to see new members!

Quote:

---

it works for selecting a date for a new entry, but it wont select a day of work from the calendar, can i not do that?

---

Are you saying that the calendar control is not allowing you to select a past date?

Quote:

---

Where i've got Pay - PAYE i need to save that to my table field 'pay' when all the other fields save but currently it has the equation for - tax in the Control Properties, any ideas on how i can transfer it to my table?

---

I don't understand this question- what are you wanting to do here? What are you needing to add to your table?

Quote:

---

Originally Posted by CreganTur

Welcome to the forum- always good to see new members!


Are you saying that the calendar control is not allowing you to select a past date?



I don't understand this question- what are you wanting to do here? What are you needing to add to your table?

---

Hi thanks!

When i selected a date on the calendar, its not responding to the data already in. For example when i select a date using date control (as seen in the image) it moves to that date displaying all the data for that date. So i can use the calender to go straight to a day, see if i worked on that day if so all the info is displayed, if not blank maybe?

I need to add the value worked out to my table, its hard to explain (as i say i'm new at all this) without the pic. :(

I've got this in the Control Source property of the textbox i want to save to my table.

=[Rate]*(Hour([Hours])+(Minute([Hours])/60))

(actually got that from this forum)

Now i want to save that displayed data to my table, but cannot link to it anymore as the above is in the control source instead of the field 'pay' which is in my table.

Is that a bit better?

An easy way to handle this would be to use an Update query like the one below:

[VBA]DoCmd.RunSQL "UPDATE TableName SET TableName.Pay = " & Me.txtbox _
& " (((WHERE ID )= " & Me.IDTextBox & "));"[/VBA]
Replace 'TableName' with the name of your table, replace 'txtbox' with the name of your Pay - PAYE textbox, and replace 'IDTextBox' with the name of your ID textbox, and change 'ID' to the name of your ID field

Note: I am assuming that the ID shown on your picture is the primary key of your table. If it isn't, then you're going to what to change the field and textbox to match your table's primary key.

You'll need to decide what event to put this code into so it runs when you want it to.


When you run this query you will get annoying messages from Access warning you that you're about to change some data. To get rid of these messages you can use:
[VBA]DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE TableName SET TableName.Pay = " & Me.txtbox _
& " (((WHERE ID )= " & Me.IDTextBox & "));"
DoCmd.SetWarnings True[/VBA]

This turns the warning messages off, and then back on. If you do not turn them back on then you'll never get another message again, even the really important ones! Be sure you always turn warnings back on!

HTH:thumb

Excellent, will have a bash at that. thanks very much, will report back in a bit on how it went.

I can now show you the GUI:

http://img361.imageshack.us/img361/5776/vba2jg9.png

This is what i've done:

Private Sub Command72_Click()
DoCmd.RunSQL "UPDATE work SET work.Pay = " & Me.Pay2 & " (((WHERE ID )= " & Me.ID2 & "));"
End Sub

Just put it in a seperate command button for now till i get it working.

Error: runtime error '3075':

syntax error (missing operator) in query expression '18.336 (((WHERE ID)) = 1))'

So its pulling the value through, just not sure why its not saving.

PS: My ID is Primary Key in the table.

Try this:

[vba]Private Sub Command72_Click()
DoCmd.RunSQL "UPDATE work SET work.Pay = " & Me.Pay2 & " WHERE ID = " & Me.ID2 & ";"
End Sub
[/vba]

Sometimes when you mix VBA and SQL they get a little strange with each other. The parantheses were probably the issue.

Also, it is not best practice to use generic names for objects, like Command72. Renaming your objects with unique names can prevent a lot of problems, and it makes it easier to code with them since you can tell what they are by their unique names :thumb

That worked perfectly, thank you very much for your time and help, much appreciated.

(renamed the button as well ;))

PS: To get rid of the warning: (if you've got time)

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE work SET work.Pay = " & Me.Pay2 & " WHERE ID = " & Me.ID2 & ";"
DoCmd.SetWarnings True

But it didn't work, its still warning





EDIT: Nevermind, got it working thanks.