Please help if you can, I am new to Access2000 and need the help. I have form which the user enters an accident date into a textbox. Then selects the company name from a combo box. Based on this input I need to populate another textbox with a policy number using the company name and the date entered is between the effective and expiration dates.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
combo and textbox help
- Thread starter BDW1969
- Start date
BDW1969,
Sounds like insurance claims. Need a little more info to be of help to you. I can't tell if you are trying to create a policy number or trying to retrieve one. Your descripiton implies you are going to create a policy number based on the company and the accident date (that sounds more like a claim number). You mentioned effective and expiration dates but didn't really make clear how they fit in. Please answer the following questions.
1) Where are the effective and expiration dates coming from?
2) Where is the policy number supposed to come from?
3) Is the policy number supposed to go into the other text box you mentioned?
Sounds like insurance claims. Need a little more info to be of help to you. I can't tell if you are trying to create a policy number or trying to retrieve one. Your descripiton implies you are going to create a policy number based on the company and the accident date (that sounds more like a claim number). You mentioned effective and expiration dates but didn't really make clear how they fit in. Please answer the following questions.
1) Where are the effective and expiration dates coming from?
2) Where is the policy number supposed to come from?
3) Is the policy number supposed to go into the other text box you mentioned?
- Thread starter
- #3
Thanks for the quick response. It is for insurance, in answer to the questions you listed.
1. The effective and experation dates are in a table.
2. The policy number is coming from the same table as the dates. I'm not producing a policy number from this info. At some in time would like to produce a claim number from this info.
3. Yes, the last textbox is to be populated with the policy number.
I hope I have given enough info to help you help me.
1. The effective and experation dates are in a table.
2. The policy number is coming from the same table as the dates. I'm not producing a policy number from this info. At some in time would like to produce a claim number from this info.
3. Yes, the last textbox is to be populated with the policy number.
I hope I have given enough info to help you help me.
BDW1969,
Your answers are as I expected. I made an assumption that the compnay name tou refferred to was an insurance company, but I think now it means the company name of a client. SO what you really want is to use the company name selected to pull up the policy number, and also to verify that the acident date is between the effective and expiration dates of that company's policy. The two peices of information are unrelated in that your table already (probably) has the dates in it for the company's policy, regardless of whether there is an accident date. In that case, a couple of DLookups will do. You need to lookup policy number from the table where the company name = the combo box value, but you can also get effective date and expiration date with similar lookups (programmers tip - lookups are slow, but if you are only doing a few from a small table they get the job done). You didn't say that the policy dates were to be populated on the form, but unless you want VB code, I suggest you put 2 more text boxes on the form for the dates.
On the properties window for the combobox, click the Event tab. Click next to AfterUpdate, click the arrow, select EventProcedure, click the box with the three dots.
The code window wil appear and say something like
Private Sub Combo1_AfterUpdate()
End sub
Between those two lines, put the following statements:
Me![txtPolicyNumber] = DLookUp("[PolicyNumber]", "MyTableName", [Company] = '" & Me!Combo1 & "'"
Me![txtEffectiveDate] = DLookUp("[EffectiveDate]", "MyTableName", [Company] = '" & Me!Combo1 & "'"
Me![txtExpirationDate] = DLookUp("[PolicyNumber]", "MyTableName", [Company] = '" & Me!Combo1 & "'"
Then substitute the actual names of the fields and controls for the ones in the brackets. You must have text boxes for Effective and Expiration dates on your form for the last 2 statements to work.
Your answers are as I expected. I made an assumption that the compnay name tou refferred to was an insurance company, but I think now it means the company name of a client. SO what you really want is to use the company name selected to pull up the policy number, and also to verify that the acident date is between the effective and expiration dates of that company's policy. The two peices of information are unrelated in that your table already (probably) has the dates in it for the company's policy, regardless of whether there is an accident date. In that case, a couple of DLookups will do. You need to lookup policy number from the table where the company name = the combo box value, but you can also get effective date and expiration date with similar lookups (programmers tip - lookups are slow, but if you are only doing a few from a small table they get the job done). You didn't say that the policy dates were to be populated on the form, but unless you want VB code, I suggest you put 2 more text boxes on the form for the dates.
On the properties window for the combobox, click the Event tab. Click next to AfterUpdate, click the arrow, select EventProcedure, click the box with the three dots.
The code window wil appear and say something like
Private Sub Combo1_AfterUpdate()
End sub
Between those two lines, put the following statements:
Me![txtPolicyNumber] = DLookUp("[PolicyNumber]", "MyTableName", [Company] = '" & Me!Combo1 & "'"
Me![txtEffectiveDate] = DLookUp("[EffectiveDate]", "MyTableName", [Company] = '" & Me!Combo1 & "'"
Me![txtExpirationDate] = DLookUp("[PolicyNumber]", "MyTableName", [Company] = '" & Me!Combo1 & "'"
Then substitute the actual names of the fields and controls for the ones in the brackets. You must have text boxes for Effective and Expiration dates on your form for the last 2 statements to work.
- Thread starter
- #5
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question