use VBA to run query duplicate and and increment if number already in table

[mjquinon via AccessMonster.com]

ok

I have been looking through all treads but have not found one that could help
me. If i missed the tread sry for this post..

Anyways

I have a form that would ask a user to enter an id numer. The i want the
field to run a query to see if it that number is already in the table.... if
it is in the table i want it to increment the last number by one.

so

ID: BMO-151-TD05-1

if match

Then

ID: BMO-151-TD05-2 *this number can't be in the system either*

pleaseeeee help me!!!

Thanks
mike

 

[Guest]

How high would that number go? Just to 9? 99? 999?

And what would you want the result to be if all possible numbers are indeed
already in the table?

Here's an example that would work:

Dimc dbConn As ADODB.Connection
Dim recSet As ADODB.Recordset
Dim myID as String
Dim Increment as Integer
Dim NoRecordFlag as Boolean

NoRecordFlag = False
Increment = 1
myID = Me.ID & "-" & Str(Increment)

Set recSet = New ADODB.Recordset
recSet.CursorLocation = adUseClient
recSet.CursorType = adOpenForwardOnly
recSet.LockType = adLockOptimistic
recSet.ActiveConnection = Application.CurrentProject.Connection

While Not NoRecordFlag and Increment < 99
recSet.Open "select * from MyTable WHERE ID = '" & myID & "'"
if recSet.RecordCount < 1 then
NoRecordFlag = True
else
Increment = Increment + 1
myID = Me.ID & "-" & Str(Increment)
end if

recSet.Close
Wend

Set recSet = Nothing

if Increment = 99 then
' No empty slots found
else
' Do Stuff....
end if

 

[Guest]

Note the typo in the first DIM:

Dimc dbConn As ADODB.Connection

SHOULD BE

Dim dbConn As ADODB.Connection

 

[mjquinon via AccessMonster.com]

1, Dennis thanks for all your help.

2, I am getting the error "no value given for one or more parameters."
the debugger is highlighting this line of code
************************************************************************************
recSet.Open "select * from Stored_Info WHERE AR_ID = '" & myID & "'"
************************************************************************************

Stored_Info is the table I am using to store all my information
AR_ID is the field that we are working on

//myID is getting a value
myID = Me.AR_ID & "-" & Str(Increment)

//I didnt make any changes to this one because i wasn't sure of what to do.
Set recSet = New ADODB.Recordset

I hope you can help me out one more time.
Thanks again
Mike

 

[mjquinon via AccessMonster.com]

1, Dennis thanks for all your help.

2, I am getting the error "no value given for one or more parameters."
the debugger is highlighting this line of code
************************************************************************************
recSet.Open "select * from Stored_Info WHERE AR_ID = '" & myID & "'"
************************************************************************************

Stored_Info is the table I am using to store all my information
AR_ID is the field that we are working on

//myID is getting a value
myID = Me.AR_ID & "-" & Str(Increment)

//I didnt make any changes to this one because i wasn't sure of what to do.
Set recSet = New ADODB.Recordset

I hope you can help me out one more time.
Thanks again
Mike

 

[mjquinon via AccessMonster.com]

1, Dennis thanks for all your help.

2, I am getting the error "no value given for one or more parameters."
the debugger is highlighting this line of code
************************************************************************************
recSet.Open "select * from Stored_Info WHERE AR_ID = '" & myID & "'"
************************************************************************************

Stored_Info is the table I am using to store all my information
AR_ID is the field that we are working on

//myID is getting a value
myID = Me.AR_ID & "-" & Str(Increment)

//I didnt make any changes to this one because i wasn't sure of what to do.
Set recSet = New ADODB.Recordset

I hope you can help me out one more time.
Thanks again
Mike

 

[mjquinon via AccessMonster.com]

1, Dennis thanks for all your help.

2, I am getting the error "no value given for one or more parameters."
the debugger is highlighting this line of code
************************************************************************************
recSet.Open "select * from Stored_Info WHERE AR_ID = '" & myID & "'"
************************************************************************************

Stored_Info is the table I am using to store all my information
AR_ID is the field that we are working on

//myID is getting a value
myID = Me.AR_ID & "-" & Str(Increment)

//I didnt make any changes to this one because i wasn't sure of what to do.
Set recSet = New ADODB.Recordset

I hope you can help me out one more time.
Thanks again
Mike