By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,204 Members | 1,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,204 IT Pros & Developers. It's quick & easy.

Creating a value in a text box based on two other values

P: 8
Hi everybody, I'm new to the forum and Access and hope you can help with the following question using Access 2000:

I would like to have a value displayed in a text box based upon two other values.
Combo box called Plant - with the following choices Plant 1, Plant 2, Plant 3
Text box - called Cycle Time which is a time field (user input)
Text - called Trigg Rep, this is where I would like the value displayed based on the other two e.g.
If Plant 1 is selected with a cycle time >06:00 display Yes otherwise leave blank
If Plant 2 is selected with a cycle time>12:00 display Yes otherwise leave blank
If Plant 3 is selected with a cycle time>12:00 display Yes otherwise leave blank

I have no idea how to go about this or whether it is possible to do it, any help would be grately appreciated.
PhilS
Oct 18 '07 #1
Share this Question
Share on Google+
14 Replies


puppydogbuddy
Expert 100+
P: 1,923
Hi everybody, I'm new to the forum and Access and hope you can help with the following question using Access 2000:

I would like to have a value displayed in a text box based upon two other values.
Combo box called Plant - with the following choices Plant 1, Plant 2, Plant 3
Text box - called Cycle Time which is a time field (user input)
Text - called Trigg Rep, this is where I would like the value displayed based on the other two e.g.
If Plant 1 is selected with a cycle time >06:00 display Yes otherwise leave blank
If Plant 2 is selected with a cycle time>12:00 display Yes otherwise leave blank
If Plant 3 is selected with a cycle time>12:00 display Yes otherwise leave blank

I have no idea how to go about this or whether it is possible to do it, any help would be grately appreciated.
PhilS
Place the following code in your form's code module. In the future, you are urged to follow Access standard naming conventions, which prescribe (among other things) mixed case with standard prefixes no spaces or special characters in the names of Access objects. For example, Plant would be cboPlant,
Expand|Select|Wrap|Line Numbers
  1. Private Sub [Cycle Time]_AfterUpdate()
  2. If Plant.Value = [Plant 1] and HoursAndMinutes([Cycle Time]) > 06:00 Then
    [Trigg Rep].Value = "Yes"
    ElseIf Plant.Value = [Plant 2] and HoursAndMinutes([Cycle Time])  > 12:00 Then
    [Trigg Rep].Value = "Yes"
    ElseIf Plant.Value = [Plant 3] and HoursAndMinutes([Cycle Time])  > 12:00 Then
    [Trigg Rep].Value = "Yes"
    Else
    [Trigg Rep].Value = ""
    End If
Place the following code in a standard module:
Expand|Select|Wrap|Line Numbers
  1. Public Function HoursAndMinutes(interval As Variant) As String
  2. '*************************************************************
  3. ' Function HoursAndMinutes(interval As Variant) As String
  4. ' Returns time interval formatted as a hours:minutes string
  5. '*************************************************************
  6.   Dim totalminutes As Long, totalseconds As Long
  7.   Dim hours As Long, minutes As Long, seconds As Long
  8.  
  9.   If IsNull(interval) = True Then Exit Function
  10.  
  11.   hours = Int(CSng(interval * 24))
  12.  
  13.   ' 1440 = 24 hrs * 60 mins
  14.   totalminutes = Int(CSng(interval * 1440))
  15.   minutes = totalminutes Mod 60
  16.  
  17.   ' 86400 = 1440 * 60 secs
  18.   totalseconds = Int(CSng(interval * 86400))
  19.   seconds = totalseconds Mod 60
  20.  
  21.   ' Round up the minutes and adjust hours
  22.   If seconds > 30 Then minutes = minutes + 1
  23.   If minutes > 59 Then hours = hours + 1: minutes = 0
  24.   HoursAndMinutes = hours & ":" & Format(minutes, "00")
  25.  
  26. End Function     
Oct 18 '07 #2

P: 8
Place the following code in your form's code module. In the future, you are urged to follow Access standard naming conventions, which prescribe (among other things) mixed case with standard prefixes no spaces or special characters in the names of Access objects. For example, Plant would be cboPlant,
Expand|Select|Wrap|Line Numbers
  1. Private Sub [Cycle Time]_AfterUpdate()
  2. If Plant.Value = [Plant 1] and HoursAndMinutes([Cycle Time]) > 06:00 Then
    [Trigg Rep].Value = "Yes"
    ElseIf Plant.Value = [Plant 2] and HoursAndMinutes([Cycle Time])  > 12:00 Then
    [Trigg Rep].Value = "Yes"
    ElseIf Plant.Value = [Plant 3] and HoursAndMinutes([Cycle Time])  > 12:00 Then
    [Trigg Rep].Value = "Yes"
    Else
    [Trigg Rep].Value = ""
    End If
Place the following code in a standard module:
Expand|Select|Wrap|Line Numbers
  1. Public Function HoursAndMinutes(interval As Variant) As String
  2. '*************************************************************
  3. ' Function HoursAndMinutes(interval As Variant) As String
  4. ' Returns time interval formatted as a hours:minutes string
  5. '*************************************************************
  6.   Dim totalminutes As Long, totalseconds As Long
  7.   Dim hours As Long, minutes As Long, seconds As Long
  8.  
  9.   If IsNull(interval) = True Then Exit Function
  10.  
  11.   hours = Int(CSng(interval * 24))
  12.  
  13.   ' 1440 = 24 hrs * 60 mins
  14.   totalminutes = Int(CSng(interval * 1440))
  15.   minutes = totalminutes Mod 60
  16.  
  17.   ' 86400 = 1440 * 60 secs
  18.   totalseconds = Int(CSng(interval * 86400))
  19.   seconds = totalseconds Mod 60
  20.  
  21.   ' Round up the minutes and adjust hours
  22.   If seconds > 30 Then minutes = minutes + 1
  23.   If minutes > 59 Then hours = hours + 1: minutes = 0
  24.   HoursAndMinutes = hours & ":" & Format(minutes, "00")
  25.  
  26. End Function     
Thanks for responding quickly. I apologise for not using the conventions, unfortunatley I have never used code before, but will try to in the future.
I have tried to input the code but I am getting a Compile error: Expected: Then or GoTo in the following line:
If Plant.Value = [Plant 1] and HoursAndMinutes([Cycle Time])> 06:00 Then
The colon is greyed out which I suspect may be the problem, but do not know how to overcome this.

Many thanks for your help so far
PhilS
Oct 18 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
Thanks for responding quickly. I apologise for not using the conventions, unfortunatley I have never used code before, but will try to in the future.
I have tried to input the code but I am getting a Compile error: Expected: Then or GoTo in the following line:
If Plant.Value = [Plant 1] and HoursAndMinutes([Cycle Time])> 06:00 Then
The colon is greyed out which I suspect may be the problem, but do not know how to overcome this.

Many thanks for your help so far
PhilS
Phil,
No need for apology. That is why we are here: to give you guidance and direction.

Put quotes around the time Like this>>>>>>> "06:00"

do the same for each of the cycle times.
Oct 18 '07 #4

P: 8
Phil,
No need for apology. That is why we are here: to give you guidance and direction.

Put quotes around the time Like this>>>>>>> "06:00"

do the same for each of the cycle times.
Thanks Puppydogbuddy, that allowed me to finish entering the code. But when I run it I know receive a 'Runtime Error 2465' - Microsoft Access can't find the field '|' referred to in your expression and when I run the debug it highlights this line,

If Plant.Value = [Plant 1] And HoursAndMinutes([CycleTime]) > "06:00" Then

In my ignorance I did not mention that this is on a subform called [Production Details Subform] attached to the main form called [Production Details] would this be the reason for this.

Much appreciate your time on this.
PhilS
Oct 19 '07 #5

puppydogbuddy
Expert 100+
P: 1,923
Thanks Puppydogbuddy, that allowed me to finish entering the code. But when I run it I know receive a 'Runtime Error 2465' - Microsoft Access can't find the field '|' referred to in your expression and when I run the debug it highlights this line,

If Plant.Value = [Plant 1] And HoursAndMinutes([CycleTime]) > "06:00" Then

In my ignorance I did not mention that this is on a subform called [Production Details Subform] attached to the main form called [Production Details] would this be the reason for this.

Much appreciate your time on this.
PhilS

No problem, but I need to know specifically which objects are on the main form and which ones are on the subform. The general syntax for referencing an object on the subform from the main form is:
Me!YourSubformControl.Form!YourTextboxControl

in your case maybe:
Me![Production Details Subform].Form![Trigg Rep]???
Oct 19 '07 #6

P: 8
No problem, but I need to know specifically which objects are on the main form and which ones are on the subform. The general syntax for referencing an object on the subform from the main form is:
Me!YourSubformControl.Form!YourTextboxControl

in your case maybe:
Me![Production Details Subform].Form![Trigg Rep]???
Puppydogbuddy,

All three objects are on the [Production Details Subform]

PhilS
Oct 19 '07 #7

puppydogbuddy
Expert 100+
P: 1,923
Puppydogbuddy,

All three objects are on the [Production Details Subform]

PhilS
Phil, sorry I couldn't get back to you sooner, but my computer network went down and I just got back online.

If all 3 objects are on the subform, and you have nothing on the main form to trigger these objects, then all you need to do is place the following code (as is) behind the subform (the actual subform, not the subform container on the main form). The code for the Hours and Minutes function remains in a standard module as it is now.

Expand|Select|Wrap|Line Numbers
  1. Private Sub [Cycle Time]_AfterUpdate()
  2. If Plant.Value = [Plant 1] and HoursAndMinutes([Cycle Time]) > "06:00" Then
    [Trigg Rep].Value = "Yes"
    ElseIf Plant.Value = [Plant 2] and HoursAndMinutes([Cycle Time])  > "12:00" Then
    [Trigg Rep].Value = "Yes"
    ElseIf Plant.Value = [Plant 3] and HoursAndMinutes([Cycle Time])  > "12:00" Then
    [Trigg Rep].Value = "Yes"
    Else
    [Trigg Rep].Value = ""
    End If
  3.  
Oct 21 '07 #8

P: 8
Phil, sorry I couldn't get back to you sooner, but my computer network went down and I just got back online.

If all 3 objects are on the subform, and you have nothing on the main form to trigger these objects, then all you need to do is place the following code (as is) behind the subform (the actual subform, not the subform container on the main form). The code for the Hours and Minutes function remains in a standard module as it is now.

Expand|Select|Wrap|Line Numbers
  1. Private Sub [Cycle Time]_AfterUpdate()
  2. If Plant.Value = [Plant 1] and HoursAndMinutes([Cycle Time]) > "06:00" Then
    [Trigg Rep].Value = "Yes"
    ElseIf Plant.Value = [Plant 2] and HoursAndMinutes([Cycle Time])  > "12:00" Then
    [Trigg Rep].Value = "Yes"
    ElseIf Plant.Value = [Plant 3] and HoursAndMinutes([Cycle Time])  > "12:00" Then
    [Trigg Rep].Value = "Yes"
    Else
    [Trigg Rep].Value = ""
    End If
  3.  
No problem with the delay, appreciate your help with this. I had already put this code into the actual subform and it did not work. The only thing I can see different to what you have suggested is the following:

Private Sub [Cycle Time]_AfterUpdate() - it does not like the enclosed Cycle Time, if I do this it gives me a compile error: expected identifier. If I take out the brackets it accepts this but then gives me the problem stated previously.

Once again many thanks for your time and patience.

PhilS
Oct 22 '07 #9

puppydogbuddy
Expert 100+
P: 1,923
No problem with the delay, appreciate your help with this. I had already put this code into the actual subform and it did not work. The only thing I can see different to what you have suggested is the following:

Private Sub [Cycle Time]_AfterUpdate() - it does not like the enclosed Cycle Time, if I do this it gives me a compile error: expected identifier. If I take out the brackets it accepts this but then gives me the problem stated previously.

Once again many thanks for your time and patience.

PhilS
Phil,
If you have spaces in your object names, you have to have the brackets. Access may be having a problem with the reference, but it is not due to the brackets. Just so we know for sure, can you look up the names you have for the following objects:

Your Subform Control
Your Subform Source Object

to obtain the names of these objects, place your form in design view, highlight the border between the main form and the subform, invoke the property sheet; and look at the first 2 properties: Name refers to the subform control and Source Object refers to the actual subform.

Also, please verify that your combobox is on the subform, and not on the main form. To do this, place your actual subform in design view and make sure that the combobox shows up in the subform in design view.

Thanks.
Oct 22 '07 #10

P: 8
Phil,
If you have spaces in your object names, you have to have the brackets. Access may be having a problem with the reference, but it is not due to the brackets. Just so we know for sure, can you look up the names you have for the following objects:

Your Subform Control
Your Subform Source Object

to obtain the names of these objects, place your form in design view, highlight the border between the main form and the subform, invoke the property sheet; and look at the first 2 properties: Name refers to the subform control and Source Object refers to the actual subform.

Also, please verify that your combobox is on the subform, and not on the main form. To do this, place your actual subform in design view and make sure that the combobox shows up in the subform in design view.

Thanks.
Many thanks for a speedy reply, I have checked the following:
There are no spaces in the object names, the combo box is definitely on the subform.

Name: Production Details Subform1
Source Object: Production Details Subform

PhilS.
Oct 22 '07 #11

puppydogbuddy
Expert 100+
P: 1,923
Many thanks for a speedy reply, I have checked the following:
There are no spaces in the object names, the combo box is definitely on the subform.

Name: Production Details Subform1
Source Object: Production Details Subform

PhilS.
Place this code behind the subform you identified as [Production Details Subform]. I don't understand your statement about having no spaces in object names>>> Production Details Subform for example has 2 spaces in the name. ^ ^

If this does not work, can you email me a zipped copy of your mdb (scrub any sensitive information)?. I have MS Access 2000, so you if you want me to look at it, you must have the same version or the access97 version in order for me to be able to work with it. You can get my email address by downloading my VCard that is with my profile information.

Private Sub [Cycle Time]_AfterUpdate()
If Me!Plant = [Plant 1] and HoursAndMinutes(Me![Cycle Time]) > "06:00" Then
Me![Trigg Rep] = "Yes"
ElseIf Me!Plant = [Plant 2] and HoursAndMinutes(Me![Cycle Time]) > "12:00" Then
Me![Trigg Rep] = "Yes"
ElseIf Me!Plant.= [Plant 3] and HoursAndMinutes(Me![Cycle Time]) > "12:00" Then
Me![Trigg Rep] = "Yes"
Else
Me![Trigg Rep] = ""
End If
Oct 23 '07 #12

P: 8
Place this code behind the subform you identified as [Production Details Subform]. I don't understand your statement about having no spaces in object names>>> Production Details Subform for example has 2 spaces in the name. ^ ^

If this does not work, can you email me a zipped copy of your mdb (scrub any sensitive information)?. I have MS Access 2000, so you if you want me to look at it, you must have the same version or the access97 version in order for me to be able to work with it. You can get my email address by downloading my VCard that is with my profile information.

Private Sub [Cycle Time]_AfterUpdate()
If Me!Plant = [Plant 1] and HoursAndMinutes(Me![Cycle Time]) > "06:00" Then
Me![Trigg Rep] = "Yes"
ElseIf Me!Plant = [Plant 2] and HoursAndMinutes(Me![Cycle Time]) > "12:00" Then
Me![Trigg Rep] = "Yes"
ElseIf Me!Plant.= [Plant 3] and HoursAndMinutes(Me![Cycle Time]) > "12:00" Then
Me![Trigg Rep] = "Yes"
Else
Me![Trigg Rep] = ""
End If
Sorry for the delay in replying, I have just got home from work. I misunderstood you about the object names. However I have tried the above code and unforunately it gives me the same runtime error. My version is Microsoft Access 2002 so I therefore cannot send it to you. Many thanks for your time spent on this, I have actually learnt quite a bit from doing this anyway, so all is not lost. (I will keep plugging away)

Keep up the good work,

PhilS
Oct 23 '07 #13

puppydogbuddy
Expert 100+
P: 1,923
Sorry for the delay in replying, I have just got home from work. I misunderstood you about the object names. However I have tried the above code and unforunately it gives me the same runtime error. My version is Microsoft Access 2002 so I therefore cannot send it to you. Many thanks for your time spent on this, I have actually learnt quite a bit from doing this anyway, so all is not lost. (I will keep plugging away)

Keep up the good work,

PhilS
Phil,
Maybe this link will help you. It is a guide to reference syntax between a form and subform. You'll need to understand the syntax used is dependent on where you are calling the object from as well as where the object is located.

http://www.mvps.org/access/forms/frm0031.htm

Also, although you can't send the mdb file, maybe you can attach a snapshot of your form/subform

There are tutorials on this site that may help you also.
Oct 23 '07 #14

P: 8
Phil,
Maybe this link will help you. It is a guide to reference syntax between a form and subform. You'll need to understand the syntax used is dependent on where you are calling the object from as well as where the object is located.

http://www.mvps.org/access/forms/frm0031.htm

Also, although you can't send the mdb file, maybe you can attach a snapshot of your form/subform

There are tutorials on this site that may help you also.
Thanks for that information - will have a look at it. Also I will try and send you some snapshots. (Won't be able to do this for a couple of days due to work committments).

Many thanks
PhilS
Oct 25 '07 #15

Post your reply

Sign in to post your reply or Sign up for a free account.