Assign variable to Recordset Field

[oldguy]

Hi.... I am a new to Access (1 month) and am stuck on a problem.

Table PermitType
5 Records with 2 fields
Example: RA, Residential Addition

Table PermitNum
1 Record with 5 fields
Example: RA,RG,RN,RO,RR
This table is used as a counter used for the number of Building Permits issued by type.

There is a combo box that looks up a Building Permit Type (2 Letters, i.e. RA, RG, RN, RO and RR) and the accompanying Descripition in Table "PermitType".

I am wanting to then take that 2 letter Building Permit Type (i.e. RA) and find the corresponding field in Table PermitNum and increment the corresponding value.

What is the correct syntax to use? See the bold area below.

Thanks so much in advance for any help on this. Hopefully I have made myself clear.
Oldguy

Dim Tempnum As Integer
Dim Strvar As String

' Assign Type Of Permit To Field Pnum_ptype (Lookup From Table PermitType)
[Pnum_ptype] = Combo15.Column(0)

' Assign Description Of Permit To Field Pnum_ptyped (Lookup From Table PermitType)
[Pnum_ptyped] = Combo15.Column(1)

' Assign Type Of Permit To Variable Strvar
Strvar = Combo15.Column(0)

Tempdb = "PermitNumTable"
Set dbs = CurrentDb()
Set Rst = dbs.OpenRecordset(Tempdb)
Rst.Edit
' Rst!??? = Rst!??? + 1 Here is where I need help !!!
Rst.Update
' ...... Additional Code Assigning Permit Number, etc....
Rst.Close
Set Rst = Nothing
Set db = Nothing

 

[jzwp22]

Welcome to the forum!

Are the 5 permit fields of the PermitNum table related to one another or related to someone/something that requested a particular permit to do something?

 

[jzwp22]

Typically you would record all of the permits that are created along with the type for each. Each created permit would be a record in the table. As you add new permit records, you would increment the permit number by 1 for each type.

For example, let's say that an RA permit with the number of # 565 was issued to a person. Then you get a request for another RA permit, when you enter this new permit record you would find that last RA permit number (i.e. 565) and increment it to 566. You would do the same for an RG permit. For example, let's say that the last RG permit # issued was 1001; the next new RG permit would then be incremented to 1002. The table structure would look like this

tblPermitTypes
-pkPermitTypeID primary key,autonumber
-txtAbbr
-txtLongName

tblPermitsIssued
-pkPermitIssuedID primary key, autonumber
-fkClientID foreign key to a table holding who the permit is issued to
-fkPermitTypeID foreign key to tblPermitType
-PermitNumber (this is the field that would be incremented based on type)
-dtePermit (date of the permit)
etc.

 

[oldguy]

Thanks so much for the quick response. I really appreciate it. Sorry I did not give you enough detail. Hopefully you will have a better understanding after this.

The PermitNum table is used to hold counter information. Permit numbers are made up of the Building Permit Type (2 letters i.e. RA, RG, etc…), the date (format yymmdd), a dash and then a 2 digit number (the counter number from table PermitNum. Here is an example: RA130113-04 which represents the 4th Residential Addition Permit which was issued on January 13th, 2013.

What I am trying to do is to increment the counter by 1 for the appropriate field (which was selected in combo box lookup) then use that value in the Permit Number as described above.

Looking at your 2nd response, you have it right on. My question is the code to do the increment.

Again, thanks. Oldguy

 

[jzwp22]

Since the permit number RA130113-04 is constructed, it would be a calculated value, so it would not be stored in your table but constructed and displayed on the fly when you need it. You will of course need to store the components that go into making the number.

As to the code, you would use the DMax() domain aggregate function and add 1 to it. Typically you would do this on a form & you could use the after update event of the combo box where you select the type to execute the code. The date field would have to be populated before the combobox. Further since you are limiting the permit number to only 2 characters, I assume that you will not have more than 99 permits of a particular type issued on a particular date. Using the table/field names I presented in my earlier post, the code would look like this:

me.permitnumbercontrolname=DMax("PermitNumber","tblPermitsIssued", "fkPermitTypeID=" & me.permittypecomboboxname & " AND dtePermit=#" & me.permitdatecontrolname & "#") +1


Of course, initially you may not have any permit numbers for certain permit types on a certain date, so you would have to check that first. If it is the first permit number of a particular type on a particular date, you, of course, would assign 1. You would use an IF..THEN..ELSE...END IF statement in conjunction with the DCount() domain aggregate function

IF DCOUNT("*","tblPermitsIssued", "fkPermitTypeID=" & me.permittypecomboboxname & " AND dtePermit=#" & me.permitdatecontrolname & "#") =0 THEN

me.permitnumbercontrolname=1

ELSE

me.permitnumbercontrolname=DMax("PermitNumber","tblPermitsIssued", "fkPermitTypeID=" & me.permittypecomboboxname & " AND dtePermit=#" & me.permitdatecontrolname & "#") +1

END IF

The me. is shorthand notation for the current form. You will have to substitute the applicable form control names where indicated.

 

[oldguy]

I can't tell you how much I appreciate your help. You have got me going in the right direction. Again, I really appreciate it.

 

[jzwp22]

You're welcome. Glad we could help out.