Need code help!

[nuttychick]

Hope someone can help - not got loads of experience with VB - It an enherited database running on Access 97, my ws is running on Win2000.

I have the following code on an onclick event of a cmd button. Its basic function is to take the number that has been entered onto the form (always a 4 digit code e.g. 56AA) and check if there are any occurances in the table.stuides like that code (e.g 56AA-1) If there are any occurances then it needs to take the highest numbered one (e.g 56AA-2) and increment the number at the end by 1. e.g.(56AA-3)
At the moment however, if it finds 56AA-1, it makes the new number 56AA-1-2
I've looked by can't identify what actual part of the code is making this change. I've included the majority of the code and I think its is the section I have made red but I don't know!
Free baby bell's my hero!


If InStr(1, Trim([Forms]![frmCloneStudy].[CloneStudyCode]), "-") <> 0 Then
strStudyCode = Left(Trim([Forms]![frmCloneStudy].[CloneStudyCode]),
Len(Trim([Forms]![frmCloneStudy].[CloneStudyCode])) - 2) & "*"
Else
strStudyCode = Trim([Forms]![frmCloneStudy].[CloneStudyCode]) & "*"
End If
'strStudyCode = Mid(Trim([Forms]![frmCloneStudy].[CloneStudyCode]), 1, 4) &
"*"
'Below including Nicki's new where statement to exclude design studies as
per JDB0014 added 18/05/05
MySQL = "SELECT studies.*, studies.Feasibility_code,
business_streams.business_stream_desc, Study_Types.Description,
Study_managers.FullName AS StudyMgr, people.FullName AS AccManager,
people_1.FullName AS ProjManager, people_2.FullName AS Architect,
Study_managers_1.FullName AS ProposalMgr, people_3.FullName AS Sponsor,
people_4.FullName AS DRSContact, BusinessDev.FullName AS BusDev,
BMSDProjMgr.FullName AS BMSDProjMgr "
MySQL = MySQL + "FROM ((Study_managers INNER JOIN ((((((((studies INNER JOIN
business_streams ON studies.business_stream_id =
business_streams.business_stream_id) INNER JOIN Study_Types ON
studies.study_type_id = Study_Types.study_type_id) LEFT JOIN people ON
studies.Acc_Man_id = people.people_id) LEFT JOIN people AS people_1 ON
studies.Proj_Man_id = people_1.people_id) LEFT JOIN people AS people_2 ON
studies.Arch_Man_id = people_2.people_id) LEFT JOIN Study_managers AS
Study_managers_1 ON studies.BDT_Man_id = Study_managers_1.Study_manager_id)
LEFT JOIN people AS people_3 ON studies.Sponsor_id = people_3.people_id)
LEFT JOIN people AS people_4 ON studies.SPD_id = people_4.people_id) ON
Study_managers.Study_manager_id = studies.study_man_id) LEFT JOIN
BusinessDev ON studies.BusinessDevID = BusinessDev.BusDevID) LEFT JOIN
BMSDProjMgr ON studies.BMSDProMgrID = BMSDProjMgr.BMSDProjMgr_id "
MySQL = MySQL + "Where
(((studies.designstudy)=No)AND(studies.Feasibility_code) Like '" &
strStudyCode & "')"
'Mike's orgional where code MySQL = MySQL + "WHERE
((studies.Feasibility_code) Like '" & strStudyCode & "') "
'MySQL = MySQL + "WHERE ((studies.Feasibility_code) Like '" +
Trim(Mid([Forms]![frmCloneStudy].[CloneStudyCode], 1, 4)) & "*" + "') "
MySQL = MySQL + "ORDER BY studies.Feasibility_code DESC; "
Set tempSet = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)
' Get the Cloned Study index number
strStudyCode = tempSet![studies.Feasibility_code]
If CountRecordsInTable(MySQL) = 1 Then
intIndex = 1
Else
intPos = InStr(strStudyCode, "-") + 1
intIndex = Val(Mid(strStudyCode, (InStr(strStudyCode, "-") + 1))) + 1
End If
strIndex = Str(intIndex)
NewStudyCode = UCase(strStudyCode) & "-" & Trim(strIndex)
NewStudyTitle = MySet!study_title & " (" & Trim(strIndex) & ")"

 

[KenHigg]

Nicki, You may get more interest in your problem if you make your posted code easier to read. See the second item in the second post in this thread.

(and hope you can get Mr. McAbney to look at your code! )

 

[nuttychick]

Code

Thanks Ken, it did look a mess!

If InStr(1, Trim([Forms]![frmCloneStudy].[CloneStudyCode]), "-") <> 0 Then
strStudyCode = Left(Trim([Forms]![frmCloneStudy].[CloneStudyCode]),Len Trim([Forms]![frmCloneStudy].[CloneStudyCode])) - 2) & "*"
Else
strStudyCode = Trim([Forms]![frmCloneStudy].[CloneStudyCode]) & "*"
End If
'strStudyCode = Mid(Trim([Forms]![frmCloneStudy].[CloneStudyCode]), 1, 4) &
"*"
'Below including Nicki's new where statement to exclude design studies as
per JDB0014 added 18/05/05
MySQL = "SELECT studies.*, studies.Feasibility_code,business_streams.business_stream_desc, Study_Types.Description,Study_managers.FullName AS StudyMgr,people.FullName AS AccManager,people_1.FullName AS ProjManager, people_2.FullName AS Architect,Study_managers_1.FullName AS ProposalMgr, people_3.FullName AS Sponsor,people_4.FullName AS DRSContact, BusinessDev.FullName AS BusDev,BMSDProjMgr.FullName AS BMSDProjMgr "
MySQL = MySQL + "FROM ((Study_managers INNER JOIN ((((((((studies INNER JOIN business_streams ON studies.business_stream_id = business_streams.business_stream_id) INNER JOIN Study_Types ON studies.study_type_id = Study_Types.study_type_id) LEFT JOIN people ON studies.Acc_Man_id = people.people_id) LEFT JOIN people AS people_1 ON studies.Proj_Man_id = people_1.people_id) LEFT JOIN people AS people_2 ON studies.Arch_Man_id = people_2.people_id) LEFT JOIN Study_managers AS Study_managers_1 ON studies.BDT_Man_id =Study_managers_1.Study_manager_id) LEFT JOIN people AS people_3 ON studies.Sponsor_id = people_3.people_id)LEFT JOIN people AS people_4 ON studies.SPD_id = people_4.people_id) ON Study_managers.Study_manager_id = studies.study_man_id) LEFT JOIN BusinessDev ON studies.BusinessDevID = BusinessDev.BusDevID) LEFT JOIN BMSDProjMgr ON studies.BMSDProMgrID = BMSDProjMgr.BMSDProjMgr_id "
MySQL = MySQL + "Where (((studies.designstudy)=No)AND studies.Feasibility_code) Like '" & strStudyCode & "')"
'Mike's orgional where code MySQL = MySQL + "WHERE(studies.Feasibility_code) Like '" & strStudyCode & "') "
'MySQL = MySQL + "WHERE ((studies.Feasibility_code) Like '" +Trim(Mid([Forms]![frmCloneStudy].[CloneStudyCode], 1, 4)) & "*" + "') " 
MySQL = MySQL + "ORDER BY studies.Feasibility_code DESC; "Set tempSet = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)
[COLOR=Red]' Get the Cloned Study index number - think this is where the problem lies[/COLOR][COLOR=DarkRed]
strStudyCode = tempSet![studies.Feasibility_code]
If CountRecordsInTable(MySQL) = 1 Then
intIndex = 1
Else
intPos = InStr(strStudyCode, "-") + 1
intIndex = Val(Mid(strStudyCode, (InStr(strStudyCode, "-") + 1))) + 1
End If
strIndex = Str(intIndex)
NewStudyCode = UCase(strStudyCode) & "-" & Trim(strIndex)
NewStudyTitle = MySet!study_title & " (" & Trim(strIndex) & ")"[/COLOR]

 

[Mile-O]

I think the first thing is to ask why there are tables called people_1, people_2, etc.

My first step would be to either condense them into one table or make a UNION query to join them up and reduce the amount of SQL here.

 

[nuttychick]

people_1 and _2

Thank you for your response!

Probably best not to ask - there have been many instances where I have asked those sort of questions!
Kind of going along the 'if it ain't broke don't fix it' theory! ...although it does run like a dog!!!

There is actually only one table called people in the database.
There are lots of peoples names invloved in each record, so I think maybe it is calling all of them. (the process in whole makes a new record that is a copy of the one provided on the form - this information would therefore be copied over)??? maybe thats why?

 

[nuttychick]

Confirm only 1 people table

Actually,
I've just taken a look at the select statement and can confirm that there is only one people table.

The studies table however contains different ID's such as ProjManagerID, ArchitectID - in this select statment the peoples table is linked more than once to the studies table to get the fullname of each of the persons involved in the study.

Thanks!

 

[Bat17]

If it is just the numbering system you are after at the moment then try

Else
intPos = InStr(strStudyCode, "-")
intIndex = Val(Mid(strStudyCode, (intPos) + 1)) + 1
End If
strIndex = str(intIndex)
NewStudyCode = UCase(Left(strStudyCode, intPos - 1)) & "-" & Trim(strIndex)


In your red code.

Probably doesnt need the trims and ucases but the do no harm

Peter

 

[nuttychick]

I think your great!

Thanks Bat!!!

I think I love u! Where should I send the baby bell's?

Dont suppose you could kind of explain what this is actually doing for me?
Is it baiscally saying that the new study code is the strStudyCode and then the intPos -1 tells it where to place the new number? (calculated by the +1 in the first bit)

Else
intPos = InStr(strStudyCode, "-")
intIndex = Val(Mid(strStudyCode, (intPos) + 1)) + 1
End If
strIndex = str(intIndex)
NewStudyCode = UCase(Left(strStudyCode, intPos - 1)) & "-" & Trim(strIndex)

 

[KenHigg]

This finds the position of the "-" and assigns the value to intPos

intPos = InStr(strStudyCode, "-")

The gets the next charactor past the value of intPos and adds 1 to it then assigns the value to intIndex

intIndex = Val(Mid(strStudyCode, (intPos) + 1)) + 1

This converts intIndex to a string and assigns it to strIndex

strIndex = str(intIndex)

This grabs the first part of strStudyCode combines it with a dash, and adds the strIndex string to it.

NewStudyCode = UCase(Left(strStudyCode, intPos - 1)) & "-" & Trim(strIndex)

All of this looks like it increments a portion of the string in strStudyCode...

 

[nuttychick]

Thank You!

Thanks Ken!

I've been able to sucessfully amend my code now so that it works with all occurances! Thank you so much for yours and Bats help!