YAY!
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Dec 13, 2016, at 3:37 PM, david.pratt@outlook.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
After moving the If Numeric test to the AfterUpdate event of the ParameterID combo box, IT ALL WORKS!
This was painful but educational. THANK YOU!
---In MS_Access_Professionals@yahoogroups.com, <david.pratt@...> wrote :
OK so this is a painful learning process, but I see why you asked the question about the names of the controls. The control names were prefixed with "txt" so the LOCKED property was invalid when I used the field name instead of the control name. And that is why the only choice was VALUE when I tried to type it in. I remember reading about this and that is why I added the txt prefix to the control name. It just did not register to me that we setting the control to locked and not the field value. Now it makes sense when to use the control name and when to use the field name.
And your assessment is correct that the Invalid Use of Null message is appearing because the field Numeric is blank when a new record is being created in the subform. So that brings us to the correction you suggested of setting Numeric to True if we are creating a new record in the subform.
When this procedure is added, it causes other problems, apparently related to the record source of the subform which is a query. The tblServiceReportDetails does not contain the Numeric field, it only contains the ParameterID field. So to get Numeric into the recordset, the query contains the Parameters table, which contains the Numeric field. And in the Parameters table, all of the Parameters do have a Yes or No value in the Numeric field.
When I added the IF clause to set Numeric to true if on a new record, it results in a Many to One Join Key error message in the ParameterID combo box. Seems that is likely because we set Numeric to True before we had selected a ParameterID. IF I tab over the Parameter combo box to the ResultDec field, I get the error message, YOU TRIED TO ASSIGN THE NULL VALUE TO A VARIABLE THAT IS NOT A VARIANT TYPE. I am not sure where this is coming from since we are not trying to set anything to NULL.
While I am waiting to hear back from you, I am going to move the "IF ME.NUMERIC" test to the AfterUpdate event on the ParameterID field. The Numeric field will not have a value until the parameter is selected.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
What kind of control is ResultsTxt and ResultsDec? Do the names of the controls match the field names, or did you give them some other name? Also, if the Numeric field does not have a default value, you'll get invalid use of Null when you move to a new record. You can fix it like this:
Private Sub Form_Current()
' If on a new record,
If Me.NewRecord Then
' Make sure Numeric has a value
Me.Numeric = True
End If
If Me.Numeric Then
Me.ResultsTxt.Locked = True
Me.ResultsDec.Locked = False
Else
Me.ResultsTxt.Locked = False
Me.ResultsDec.Locked = True
End If
End Sub
You should probably also have code in AfterUpdate of the Numeric field if the user can change that.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Dec 13, 2016, at 4:36 AM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Another unsuccessful hour. This VBA code is killing me but I am learning.
The procedure under form current is not working. A couple of times I received an error saying invalid use of Null; then the last time I got a message saying something about not recognizing the Locked property. When I try to use the "help" system to type the statement, the "locked" property is not a choice for the control.
The other procedure works fine.
Thank you for all your help.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
I recommend you use a Continuous form - you'll have much more control over the layout.
These both go in the subform module:
Private Sub Form_Current()
If Me.Numeric Then
Me.ResultsTxt.Locked = True
Me.ResultsDec.Locked = False
Else
Me.ResultsTxt.Locked = False
Me.ResultsDec.Locked = True
End If
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Numeric Then
If IsNull(Me.ResultsDec) Then
MsgBox "You must enter a result in the Decimal field."
Cancel = True
End If
Else
If Len(Me.ResultsTxt & "") = 0 Then
MsgBox "You must enter a result in the Text field."
Cancel = True
End If
End IF
End Sub
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Dec 11, 2016, at 2:10 PM, David Pratt david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
In my Parameters table I will add two descriptive fields
- One field is a Yes/No named Numeric with default YES
- One field is named Comparator. It needs to be a List with choices of "=, <, > "
The input form will contain the Comparator field and the user will have to select the comparator for each result, but the default is "=" so input is easy. I probably will not even include a Tab Stop on that field since it is seldom that the Comparator is anything other than "=". Any reports will need to concatenate the Comparator field and Result field, but hide the comparator if the comparator is "=", and also hide the Comparator if the Result is a text. I will have to delay thinking about how to do this until later.
For now I need more guidance on the input form (datasheet based on tblServiceReportDetails). If the Parameter in the current record has Numeric = Yes, I want to force the user to input into the ResultsDec field, and if the Parameter in the current record has Numeric = No, I want to force the user to input into the ResultsTxt field. Can you give more details on how to code this and on what Event?
Thank you,
David
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (17) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar