Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Location
    Bournemouth, Dorset, England
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    code - concatanating various elements to make ID (Access 97)

    Hi, I've been creating a new form to add a unit to a database - I haven't got as far as adding to the db yet as I cant seem to get the last bit of my concatanation to work - third line up from the bottom - It worked fine until I added that last var and now it doesn't like it (type mismatch). As my programming "skills" consist of copying previous work I wondered if anyone could tell me what I'm doing wrong? Thank you very much.

    'procedure to add a new unit to the database
    Private Sub cmdAddUnit_Click()

    Dim wrk As Workspace
    Dim dbs As Database

    Dim strMaxUnitNo As String 'holds the SQL statement to assertain the maximum Unit_no
    Dim rstMaxUnitNo As DAO.Recordset 'holds the results from the SQL statement
    Dim rstUnitNo As DAO.Recordset 'holds all the records from the table Units
    Dim intMaxUnitNo As Integer 'holds the maximum unit number

    Dim varNewId As Variant 'holds the new unit number as concatanation of various components
    Dim varLevel As Variant 'holds the level of the unit (i.e. O, C, I, H or M)
    Dim intLevel As Integer 'holds the Level_ID from cboLevel as selected by the user on the form
    Dim VarCC1 As Variant 'holds the 1st cost centre of the unit (i.e. 05 or 06)
    Dim intCC1 As String 'hold the CC_ID from cboCC1 as selected by the user on the form
    Dim VarCC2 As Variant 'holds the 2nd cost centre of the unit (i.e. 05, 06 or 00)
    Dim intCC2 As String 'hold the CC_ID from cboCC2 as selected by the user on the form
    Dim intNewUnitNo As Integer 'holds the new unit number
    Dim varNewUnitNo As Variant 'hold the new unit number

    Set wrk = DBEngine(0)
    Set dbs = CurrentDb

    'check user has selected all required field on the form

    If (IsNull(Me.txtUnitTitle) Or IsEmpty(Me.txtUnitTitle) Or (Me.txtUnitTitle = " ")) Then
    MsgBox "Please enter the Unit Title", vbInformation
    Me.txtUnitTitle.SetFocus
    Exit Sub
    End If

    If (IsNull(Me.cboLevel) Or IsEmpty(Me.cboLevel) Or (Me.cboLevel = " ")) Then
    MsgBox "Please select the Level of the Unit", vbInformation
    Me.cboLevel.SetFocus
    Exit Sub
    End If

    If (IsNull(Me.cboCC1) Or IsEmpty(Me.cboCC1) Or (Me.cboCC1 = " ")) Then
    MsgBox "Please select Cost Centre one", vbInformation
    Me.cboCC1.SetFocus
    Exit Sub
    End If

    If (IsNull(Me.cboCC2) Or IsEmpty(Me.cboCC2) Or (Me.cboCC2 = " ")) Then
    MsgBox "Please select Cost Centre two", vbInformation
    Me.cboCC2.SetFocus
    Exit Sub
    End If

    If (IsNull(Me.cboSelectCredits) Or IsEmpty(Me.cboSelectCredits) Or (Me.cboSelectCredits = " ")) Then
    MsgBox "Please select the number of credits of this unit", vbInformation
    Me.cboSelectCredits.SetFocus
    Exit Sub
    End If

    'find the maximum Unit_no from the table Units
    strMaxUnitNo = "SELECT MAX(Unit_no) AS maxUnitNo FROM Units"
    Set rstMaxUnitNo = dbs.OpenRecordset(strMaxUnitNo)
    intMaxUnitNo = rstMaxUnitNo!maxUnitNo
    Me.txtTest = intMaxUnitNo

    'if there is not a maximum unit number then make intMaxUnitNo = zero
    If IsNull(intMaxUnitNo) Then
    intMaxUnitNo = 0
    End If

    intNewUnitNo = Me.txtTest + 1
    Me.TEMP = intNewUnitNo
    varNewUnitNo = intNewUnitNo

    'refresh & requery data behind form
    With Me
    .Refresh
    .Requery
    End With

    intLevel = Me.cboLevel
    intCC1 = Me.cboCC1
    intCC2 = Me.cboCC2

    varLevel = DLookup("[Level]", "Levels", "[Level_ID] = " & intLevel)
    VarCC1 = DLookup("[cost_centre]", "Cost_Centres", "[CC_ID] = " & intCC1)
    VarCC2 = DLookup("[cost_centre]", "Cost_Centres", "[CC_ID] = " & intCC2)



    varNewId = "IH" + VarCC1 + VarCC2 + varLevel + varNewUnitNo
    Me.txtNewUnitID = varNewId

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: code - concatanating various elements to make ID (Access 97)

    It is better to use & to concatenate strings. Concatenating with + is useful in some circumstances but probably not here. So try

    varNewId = "IH" & VarCC1 & VarCC2 & varLevel & varNewUnitNo

    If you still get an error message, select Debug and inspect the value of the various parts by hovering the mouse pointer over them. Perhaps you'll find out what is anomalous.

    BTW, you never use the wrk variable, I don't think it is needed.

  3. #3
    New Lounger
    Join Date
    Jan 2004
    Location
    Bournemouth, Dorset, England
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code - concatanating various elements to make ID (Access 97)

    Thank you very much. I'll give this a go this morning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •