Automation

[LMRollins]

I have a form that contains a subdatasheet. When you enter a quote number, it will show any previous quotes entered with the same quote number and revision number. This way the person entering the info knows what the next revision number should be. For example.

Form entry:
quote #: 010204

subdatasheet:
quote #: 010204 rev#: B
quote #: 010204 rev#: A

So that would mean the next revision number should be C. What I need to do is when the quote number is entered I need it to automatically put a check mark in a check box of the previous quote# record. So what I would end up with is Rev A would be checked when B was entered and B would be checked off when C was entered. Any suggestions?

 

[GeekGirlau]

Paste the following code into the AfterUpdate event of the sub form:

Dim strSQL As String


Select Case Asc(Me.Rev)
' Revision a or A - do nothing
Case 65, 97

' all other letters both upper and lowercase
Case 66 To 90, 98 To 122
strSQL = "UPDATE tblRevision " & _
"SET Confirmed = True " & _
"WHERE (Quote=" & Me.Quote & &quot AND " & _
"(Rev='" & Chr(Asc(Me.Rev) - 1) & "')"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Me.Requery

Case Else
MsgBox "Please enter a letter from A to Z", vbExclamation, _
"Invalid Revision Number"
End Select

 

[LMRollins]

I don't believe this will work. When a quote number is first entered it has no revision letter at all. When a change is made then it's give a revision letter. I want it to see that that quote number is already in the database and put a check mark in the previous quote if it can be done.

 

[GeekGirlau]

Never fear LM - all things are possible given time and money.


Dim strSQL As String


If Not IsNull(Me.Rev) Then
Select Case Asc(Me.Rev)
' Revision a or A - do nothing
Case 65, 97

' all other letters both upper and lowercase
Case 66 To 90, 98 To 122
strSQL = "UPDATE tblRevision " & _
"SET Confirmed = True " & _
"WHERE (Quote=" & Me.Quote & &quot AND " & _
"(Rev='" & Chr(Asc(Me.Rev) - 1) & "')"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Me.Requery

Case Else
MsgBox "Please enter a letter from A to Z", vbExclamation, _
"Invalid Revision Number"
End Select
End If

 

[LMRollins]

Well this doesn't work either. I want it to put a check mark in the previous records with the same quote number. I don't need it to give me a warning of what the next letter should be. The purpose of the whole thing is so that when I print out the report it only shows the last quote for the series.

 

[MichaelRed]

LMRollins,

Seems like you are just trying to go 'the long way round the barn'? Numerous 'soloutions' exist (even within these fora) for obtaining and incrementing a 'value', many of which are much more sophisticated than what you are asking for here. Perhaps the use of 'Adanced Search' would be useful?



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[GeekGirlau]

LMRollins, you stated that "I want it to put a check mark in the previous records with the same quote number" - that is exactly what the code does.

The purpose of the message below the Case Else is to handle situations where your users type a character other than a letter from A to Z (or a to z), and believe me they will.

Please make sure you have tested the code before stating that "this won't work" - constructive criticism or explanations as to where the code is failing is more helpful to those of us offering you solutions free-of-charge.

I have highlighted the areas in the code where you might have to make changes depending on the names of your table and fields.

Dim strSQL As String


If Not IsNull(Me.Rev) Then
Select Case Asc(Me.Rev)
' Revision a or A - do nothing
Case 65, 97

' all other letters both upper and lowercase
Case 66 To 90, 98 To 122
strSQL = "UPDATE tblRevision " & _
"SET Confirmed = True " & _
"WHERE (Quote=" & Me.Quote & &quot AND " & _
"(Rev='" & Chr(Asc(Me.Rev) - 1) & "')"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Me.Requery

Case Else
MsgBox "Please enter a letter from A to Z", vbExclamation, _
"Invalid Revision Number"
End Select
End If

 

[LMRollins]

Sorry I didn't mean to offend. I won't bother you anymore. Thanks for your help.

 

[MichaelRed]

Not 'offended', just offering anonther approach to getting a soloution.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[GeekGirlau]

All is forgiven LM - please excuse my bad temper

How did you go - were you able to get this working for you?

 

[Craig0201]

Geek,

Shouldn't that be two events? You should check the validity of the entry in the BeforeUpdate and if valid, update in the AfterUpdate event? If it fails the validation then you can Cancel the AfterUpdate event.

Craig

 

[GeekGirlau]

You're right Craig - good pickup!

The BeforeUpdate event should be ...

If Not IsNull(Me.Rev) Then
Select Case Asc(Me.Rev)
' correct revision letters - do nothing
Case 65 to 90, 97 to 122

' user has entered strange character
Case Else
MsgBox "Please enter a letter from A to Z", vbExclamation, _
"Invalid Revision Number"

Cancel = True
End Select
End If

Then the AfterUpdate event becomes ...

Dim strSQL As String


If Not IsNull(Me.Rev) Then
Select Case Asc(Me.Rev)
' all letters from b to z in both upper and lowercase
Case 66 To 90, 98 To 122
strSQL = "UPDATE tblRevision " & _
"SET Confirmed = True " & _
"WHERE (Quote=" & Me.Quote & &quot AND " & _
"(Rev='" & Chr(Asc(Me.Rev) - 1) & "')"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Me.Requery
End Select
End If