VBA for Access Help

[JockVSJock]

I was successful in converting an excel spreadsheet from excel to access, but my boss wants some 'bells & whistles.' And the only way this can be done is by using some VBA.

For Example: There is a field we have dated for a future date, we would like to code something in VBA to set up a reminder, perhaps an e-mail (we use lotus notes) or some sort of pop-up box to serve as a reminder. Is there any examples of code out there for this?

And, I have a drop down box with a few choices. If I select one, I would like it to XXXXX out other fields to indicate to an end user not to fillout those fields. Any code ideas for this?

Also, are there any websites with some examples for VBA, I don't know VB but a yr of C++ so I do know how to program.

thanks,

-Chris

 

[JoaoTL]

Hi, relative to your 1st question,i thik i heve something at home i will post later.

2. Question, if i understanded if a condition from your combo have XXXX you whould like to disable other controls, try this.

Combo OnAfterUpdate()

'Disable all Controls

Me.Field1.Enable = False
...
...

If Me.Combo.Value = xxxx then

'Now Enable what you whant

Me.Field1ToEnable = True
....
....
End If


3. Question

http://www.mvps.com/access

http://www.jtl.co.pt

- See VBA
Look in

http://www.google.com

for "access vba"

Hope it helps
Best Regards

---
JoaoTL
mail@jtl.co.pt
MS Access Site:

http://www.jtl.co.pt

The Page:

http://www.jtl.co.pt/Access

moved to

http://www.jtl.co.pt

 

[JoaoTL]

Sorry, remove the:

'Now Enable what you whant

Me.Field1ToEnable = True
....
....


Put this:

'Now Enable what you whant

Me.Field1ToEnable.Enable = True
....
....
Best Regards

---
JoaoTL
mail@jtl.co.pt
MS Access Site:

http://www.jtl.co.pt

The Page:

http://www.jtl.co.pt/Access

moved to

http://www.jtl.co.pt

 

[Lightning]

For your first question, try this:

Private Sub YourForm_OnCurrent()

If [YourFutureDateField] <= (Date + 14) AND IsNull[FutureActionField] Then
MsgBox &quot;Time to Do Something&quot;
End If

What this says is that if the date field for future action is less than or equal to Today's (system) date plus 14 days, And the field that denotes whether the action has been taken or not is null, then display a message box reminding the user that action needs to be taken. This message will open every time the record is looked at until something is entered into the Action taken field.

By putting this code into the form's On Current event, it will run every time any record that meets the criteria is opened for viewing.

HTH

Lightning

 

[JockVSJock]

This response is to Lightning:

Thanks for your help so far, but I am still having trouble listed below.

Here is the code that I am using below. 2nd Bonus and 2nd Bonus Sent are the two fields that I am using. When someone comes into our company, they get a hiring bonus, and within a year they will get a 2nd bonus, which is the future date in the 2nd Bonus. The 2nd Bonus Sent is when the amount is sent to that person. But I keep getting a 'Compile Error: Expected: Then or GoTo.'


Private frmMainForm_OnCurrent()
If[2nd Bonus]<=(11/6/2002)AND IsNull
[2nd Bonus Sent]
Then
MsgBox &quot;2nd Bonus Reminder&quot;
End If

End Sub

 

[Lightning]

Try this:

Private frmMainForm_OnCurrent()
If [2nd Bonus] <= #11/6/2002# AND IsNull ([2nd Bonus Sent]) Then
MsgBox &quot;2nd Bonus Reminder&quot;
End If

End Sub
The Date needs to be enclosed in the # signs to identify it as a Date.

You need the brackets around the [2nd Bonus Sent] field to tell the IsNull condition what to evaluate. You are getting the error message because Access can't find the brackets and therefore epects the end of the expression BEFORE it gets to the end of the full condition.


HTH

Lightning

 

[JockVSJock]

Lightning-

Thanks for all of your help, but when I copied the code like you have above, I get the following error:

Compile Error Expected: Then or GoTo


Also, just to let you know I have some buttons that I've added, so does this code effect where I place the code that I want to add? Because I have added this at the bottom below the code for the buttons.

thanks

-Chris

 

[Lightning]

Chris

Placement: This code should go in the On Current event of the Form, NOT in the code for any of the buttons. By putting it in the On Current event for the form, it will automatically be run every time you open the form OR move to another record.

Error Message: When the message is displayed, is there a highlight on any particular part of the code line, or just the whole line in general?

I've put together a quick test form, and the code as written is working correctly for me.
Try it again and, if it still doesn't work, please post your whole code procedure and I'll have a look at it.

Lightning

 

[JockVSJock]

I still can't get the code to work.

thanks for all of your help though...

Here it is below:



Option Compare Database
Option Explicit

Private Sub Detail_Click()

End Sub

'This is the first command button
Private Sub New_Record_Click()
On Error GoTo Err_New_Record_Click


DoCmd.GoToRecord , , acNewRec

Exit_New_Record_Click:
Exit Sub

Err_New_Record_Click:
MsgBox Err.Description
Resume Exit_New_Record_Click

End Sub

'Here is the second command button
Private Sub Close_Form_Click()
On Error GoTo Err_Close_Form_Click


DoCmd.Close

Exit_Close_Form_Click:
Exit Sub

Err_Close_Form_Click:
MsgBox Err.Description
Resume Exit_Close_Form_Click

End Sub
'Here is another command button
Private Sub Search_Click()
On Error GoTo Err_Search_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Search_Click:
Exit Sub

Err_Search_Click:
MsgBox Err.Description
Resume Exit_Search_Click

End Sub
'Here is the last command button
Private Sub Save_Click()
On Error GoTo Err_Save_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Save_Click:
Exit Sub

Err_Save_Click:
MsgBox Err.Description
Resume Exit_Save_Click

End Sub

'This is where the code begins that sends a reminder to the
'end user when we hit a certain date

Private frmMainForm_OnCurrent()
If[2nd Bonus]<=#11/6/2002# AND IsNull ([2nd Bonus Sent])
Then
MsgBox &quot;2nd Bonus Reminder&quot;
End If

End Sub

 

[Lightning]

OK Chris, I'm assuming that you did a copy and paste to post this code.

On that assumption, the problem is the Then keyword. It needs to be on the same line of code as the If statement.
If [2nd Bonus] <= #11/6/2002# AND IsNull ([2nd Bonus Sent]) Then
MsgBox &quot;2nd Bonus Reminder&quot;
End If

I copied your code into my test form and it gave me the same error message that you have been getting. As soon as I put the Then statement on the same line as the If statement, the error message stopped and the code ran perfectly.

Now I'm embarrassed - I should have picked that up when I first read your post.

Oh, well, at least your problem is resolved

Lightning s-)
It's always the simple things!!

 

[JockVSJock]

That did it, thanks!!!

One last question, where can I learn more about coding in VBA?

thanks,

-Chris

 

[Lightning]

Check these threads:

Thread181-141117

Thread181-130102