combo box trouble with disapearing value

[red_ned]

I have a combo box which takes its data from a query of a table to
distinguish valid from invalid options.
Once you drop down the choices you select from the remaining legitimate,
chosing this option then drops info from the table into other places on the
form.
To give you an idea this is a car permit database, so once you select say
Annual renewal it should
1) add the amount of time in months to the start date (which defaults to
today) and puts result into an expirey date box.
2) drop the cost of this permit into the cost box.
3) drop a small code into the permit number which prefixes a 6 digit number
that is to be typed in.

All this data is held inside the "Carpark Permit Types" table.
I got the cost to drop into the box but the expirey date adds 300 and ends
up saying xx month 1900, worse still the permit type choice vanishes so you
cant see it. I am a novice at this and have had this thrown into my lap, all
help is greatly appreciated.
What i need is the convention to add a time (in months) to a date, and a
quick guide to what im doing wrong with the coding for the dropdown box.

CODE...
Private Sub CP_Permit_Type_Change()
Dim permittype As String
Dim chargeamt As Currency
Dim dbs As Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Carpark Permit Types")
permittype = Forms![Travel Permits]![CP Permit Type].Value
'If Forms![Travel Permits]![CP Fee Due].Value <> "" Then Exit Sub

With rst
.Index = "PrimaryKey"
.Seek "=", Forms![Travel Permits]![CP Permit Type].Value
If Not .NoMatch Then
chargeamt = ![Charge]
End If

End With
Forms![Travel Permits]![CP Permit Type].Value = permittype
Forms![Travel Permits]![CP Fee Due].Value = chargeamt
End Sub

A great big THANKS IN ADVANCE! to all.

ned

 

[Duncan Bachen]

I have a combo box which takes its data from a query of a table to
distinguish valid from invalid options.
Once you drop down the choices you select from the remaining legitimate,
chosing this option then drops info from the table into other places on the
form.
To give you an idea this is a car permit database, so once you select say
Annual renewal it should
1) add the amount of time in months to the start date (which defaults to
today) and puts result into an expirey date box.
2) drop the cost of this permit into the cost box.
3) drop a small code into the permit number which prefixes a 6 digit number
that is to be typed in.

All this data is held inside the "Carpark Permit Types" table.
I got the cost to drop into the box but the expirey date adds 300 and ends
up saying xx month 1900, worse still the permit type choice vanishes so you
cant see it. I am a novice at this and have had this thrown into my lap, all
help is greatly appreciated.
What i need is the convention to add a time (in months) to a date, and a
quick guide to what im doing wrong with the coding for the dropdown box.

CODE...
Private Sub CP_Permit_Type_Change()
Dim permittype As String
Dim chargeamt As Currency
Dim dbs As Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Carpark Permit Types")
permittype = Forms![Travel Permits]![CP Permit Type].Value
'If Forms![Travel Permits]![CP Fee Due].Value <> "" Then Exit Sub

With rst
.Index = "PrimaryKey"
.Seek "=", Forms![Travel Permits]![CP Permit Type].Value
If Not .NoMatch Then
chargeamt = ![Charge]
End If

End With
Forms![Travel Permits]![CP Permit Type].Value = permittype
Forms![Travel Permits]![CP Fee Due].Value = chargeamt
End Sub

A great big THANKS IN ADVANCE! to all.

ned

Howdy Ned. Couple of things you can do to clean this up.
First, use the After Update event, rather than the change event. This
way it will fire only once after the permit type is selected.

What's the difference? Suppose that instead of using the mouse to select
the permit type, you entered the combo box via the keyboard and started
typing the permit type you wanted. Each time a new letter matched
(assuming permit types with similar names), the event would fire. This
would slow things down.

After Update only fires after the item is fully selected.

Two, whenever you work with objects, make sure you close them and kill
their references. I'm going to suggest an alternate method to what you
are doing in a second, but I wanted to touch base on this.

If you use code (like above), make sure you do a:
rst.Close
Set rst=Nothing
Set dbs = Nothing
at the end of your procedure. If you don't, you'll start to introduce
memory leaks as the objects aren't released.

Alrighty, that being said, the more efficient way to finding the data
you want is to use a Domain Aggregate function called DLookup, rather
than opening a recordset and searching for a particular record.

Private Sub CP_Permit_Type_AfterUpdate()
Dim strPermitType As String
Dim intPermitMonthLength as Integer
Dim curChargeAmt As Currency

strPermitType = Forms![Travel Permits]![CP Permit Type].Value
curChargeAmt = Dlookup("[Charge]","[Carpark Permit Types]",
"[PermitID] = '" & strPermitType & "'")
intPermitMonthLength = Dlookup("[LengthOfPermit]","[Carpark Permit
Types]", "[PermitID] = '" & strPermitType & "'")
Me.[CP Fee Due] = curChargeAmt
Me.[CP Due Date] = DateAdd("m", intPermitMonthLength, Date)

Notes:
1) You can use "Me" instead of the full form name if the code you are
running is ON the form that is calling it. It's cleaner.
2) Learn to prefix your variables. Helps you keep track of what data
types they are
3) Consider using table and field names without spaces in them. With
Spaces, you are constantly forced to enclose them in [] otherwise Access
has issues. Think about prefixes on your objects too, such as
tblCarparkPermitTypes, or even simpler tblPermitTypes (assuming the
whole application is about Carparking)
4) I'm using the Dateadd function to increase the value by the number of
months returned by the Dlookup
5) The Dlookup returns a variant. That means if no value is returned, it
will return a null. With my code above, that will break, because I'm
explicitly assigned them to be a string and an integer. I am assuming
that every value on your lookup table exists. If it doesn't, you'll need
to deal with the nulls (Lookup the NZ() function)
6) You are also setting the value of the box back to itself with these
commands: permittype = Forms![Travel Permits]![CP Permit Type].Value and
then later Forms![Travel Permits]![CP Permit Type].Value = permittype
There is no reason to assign the value of the textbox to a variable and
then assign it back if you don't actually change the value in any way!

I'd encourage you to Google some of these topics so you can read more
about the theory and application behind them, rather than this post
getting even longer.

*whew*

 

[red_ned]

Thanks for the reply I am googling as we speak still after 3 days, I'm not
the best person in the office to be doing this but its about time. I will
continue to clean the code up and try to reduce my over-all problems maybe to
something that is way shorter for the next time.
Thanks for your time for reading and replying.

ned

 

[red_ned]

Thanks for the reply I am googling as we speak still after 3 days, I'm not
the best person in the office to be doing this but its about time. I will
continue to clean the code up and try to reduce my over-all problems maybe to
something that is way shorter for the next time.
Thanks for your time for reading and replying.

ned

I have finally finished the bit of code and it still only drops the ID field
into both cost and date, its just not getting the correct column from the
table, here the code again, as i say the choice of a permit type should
access the table to query what the price and amount of months before it
expires then drop the values in the relevant boxes, Thanks again to any and
all who can help.
ned

<CODE>
Private Sub CP_Permit_Type_AfterUpdate()
Dim strPermitType As String
Dim intPermitMonthLedger As Integer
Dim curChargeAmt As Currency

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Carpark Permit Types")

strPermitType = Forms![Travel Permits]![CP Permit Type].Value
curChargeAmt = DLookup("[Charge]", "[Carpark Permit Types]", "[id] = '"
& strPermitType & "")
intPermitMonthLength = DLookup("[Length in Months]", "[Carpark Permit
Types]", "[id] = '" & strPermitType & "")
Me.[CP_Fee_Due] = curChargeAmt
Me.[CP_Expiry_Date] = DateAdd("m", intPermitMonthLength, Date)

'If Forms![Travel Permits]![CP Fee Due].Value <> "" Then Exit Sub

With rst

End With

rst.Close
Set rst = Nothing
Set dbs = Nothing

End Sub


All ideas welcomed

 

[red_ned]

Please ignore my last post, the coding was ok, few un-needed lines but the
properties of the data boxes themselves was incorrect.
I would like to say thanks for the help and i have tidied the whole database
code up and it feels much betterand is way easier to look through for
debugging.
Thanks again!

ned

 

[Duncan Bachen]

Thanks for the reply I am googling as we speak still after 3 days, I'm not
the best person in the office to be doing this but its about time. I will
continue to clean the code up and try to reduce my over-all problems maybe to
something that is way shorter for the next time.
Thanks for your time for reading and replying.

ned

I have finally finished the bit of code and it still only drops the ID field
into both cost and date, its just not getting the correct column from the
table, here the code again, as i say the choice of a permit type should
access the table to query what the price and amount of months before it
expires then drop the values in the relevant boxes, Thanks again to any and
all who can help.
ned

<CODE>
Private Sub CP_Permit_Type_AfterUpdate()
Dim strPermitType As String
Dim intPermitMonthLedger As Integer
Dim curChargeAmt As Currency

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Carpark Permit Types")

strPermitType = Forms![Travel Permits]![CP Permit Type].Value
curChargeAmt = DLookup("[Charge]", "[Carpark Permit Types]", "[id] = '"
& strPermitType & "")
intPermitMonthLength = DLookup("[Length in Months]", "[Carpark Permit
Types]", "[id] = '" & strPermitType & "")
Me.[CP_Fee_Due] = curChargeAmt
Me.[CP_Expiry_Date] = DateAdd("m", intPermitMonthLength, Date)

'If Forms![Travel Permits]![CP Fee Due].Value <> "" Then Exit Sub

With rst

End With

rst.Close
Set rst = Nothing
Set dbs = Nothing

End Sub


All ideas welcomed

Since you are no longer using the recordset, you can safely remove those
parts of your function

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Carpark Permit Types")
With rst

End With

rst.Close
Set rst = Nothing
Set dbs = Nothing

Now, the next thing to do is to find out why your DLookup isn't working.
What you need to do is to set a break point on the next line, so that
you can confirm that the value it's returning is the correct one.

curChargeAmt = DLookup("[Charge]", "[Carpark Permit Types]", "[id] = '"
& strPermitType & "")

In order for this line to work:
1) There must be a table called: Carpark Permit Types
2) There must be a field in the table called: id
3) The id field must be a string (text)
4) The value in your control Forms![Travel Permits]![CP Permit Type]
must be a string.
5) There must be a field in the table called: Charge

By setting the breakpoint, you can hover your cursor over the variables
and see what values they contain.

If you aren't returning the right values from the table, you'll never be
able to set the fields correctly.

Maybe you can post a sample of your Carpark Permit Types tables (2 or 3
rows) to show me what values it contains.

 

[red_ned]

Thanks once again for a detailed and valuable reply, im sorry you didnt see
the post just earlir saying it was all fixed.
I really am gratefull for the help.
I followed advice and i also got rid of the lines you have mentioned, so i
am a really happy bunny.
Thanks again, it really does make a difference when people respond to lesser
experienced people like me, keep up the good work.

ned