 |  |  |  |  |  |  |  |
Primary Key Issue
Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums - DaniWeb Sister Site
 |
Greetings.
Err, I just wanna store all the joined records' com_code into sComIndex for further population later.
Usually, in a DBMS, we would do it this way:
I thought it would be the same. Is it because of this: myRS!comact.com_code ? I deem it must be syntactically wrong but do not know what's the correct way.
Dim sComIndex(10) as String
:
:
Set myRS = New ADODB.Recordset
myRS.Open "SELECT * FROM comact, competency WHERE comact.com_code=competency.com_code", myDB, adOpenStatic, adLockReadOnly
listCG.Clear
Do While Not myRS.EOF
listCG.AddItem myRS!com_name
sComIndex(iIndex) = myRS!comact.com_code
iIndex = iIndex + 1
myRS.MoveNext
LoopErr, I just wanna store all the joined records' com_code into sComIndex for further population later.
Usually, in a DBMS, we would do it this way:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
Dim sComIndex(10) as String
:
:
Set myRS = New ADODB.Recordset
myRS.Open "SELECT * FROM comact, competency WHERE comact.com_code=competency.com_code", myDB, adOpenStatic, adLockReadOnly
listCG.Clear
Do While Not myRS.EOF
listCG.AddItem myRS!com_name
sComIndex(iIndex) = myRS!comact.com_code
'
'Are you always going to have less than 10 records? If not you'll go out of 'the index bounds. Also refer to fields using :
'
'myRS.fields("com_code").value
'
'what you're using will not work as the fields are part of the fields collection
'not properties of the recordset.
iIndex = iIndex + 1
myRS.MoveNext
LoopAlso rather than storing the indexes in an array why not just store them in the list box you are putting the records in using the ItemDate property.
Like this:
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
Mark Nemtsas
Time and Billing Software - Time Tracking Software - Roller Shutters - Roller Blinds -
Baby Books
Time and Billing Software - Time Tracking Software - Roller Shutters - Roller Blinds -
Baby Books
Greetings.
Hey, thanks for the advice.
What you meant by the above is to advise me to use an invisible list box rather than arrays? If I get you correctly here, I'd say that it's a good idea. I've never thought of that. Thanks!
Hey, thanks for the advice.
•
•
•
•
Also rather than storing the indexes in an array why not just store them in the list box you are putting the records in using the ItemDate property.
"Study the past if you would define the future" - Confucius
Greetings.
Ummm...one more question on Primary Keys -> set to auto increment.
Let's say I have 1,2,3,4,5 in a table already.
When I delete a record, umm, let's say record with PK = 3, is it possible to code for the records below PK=3 be shifted up + the PK value be modified to -1 each?
Ummm...one more question on Primary Keys -> set to auto increment.
Let's say I have 1,2,3,4,5 in a table already.
When I delete a record, umm, let's say record with PK = 3, is it possible to code for the records below PK=3 be shifted up + the PK value be modified to -1 each?
"Study the past if you would define the future" - Confucius
•
•
Join Date: Mar 2004
Posts: 634
Reputation: 
Solved Threads: 7
Practically a Master Poster
I know what you're asking but I'm not sure you can because it's a unique identifier for that very record, it won't work like that. If there is a solution to this however please post as I'm interested 
Formerly known as Slade.
The only way to do this is to NOT use autonumbering.
You could then write a function to do the renumbering, but why bother?
Your function would also have to take into account renumbering any foreign keys linking to this table from other tables..... in essence, nightmare, and completly unnessesary.
You could then write a function to do the renumbering, but why bother?
Your function would also have to take into account renumbering any foreign keys linking to this table from other tables..... in essence, nightmare, and completly unnessesary.
In Access 2000 definitely no. Can I suggest if you want to use primary keys for something human useful (and judging by your questions you do) that you keep track of your own numbers. That way you have full control.
It's not hard, use the MAX() SQL operator to get the highest ID to pop the next record in. Perhaps keep a second table with the ID's of deleted records and check this table for unused ID's before using the MAX() operator to put one on top of the pile.
It's not hard, use the MAX() SQL operator to get the highest ID to pop the next record in. Perhaps keep a second table with the ID's of deleted records and check this table for unused ID's before using the MAX() operator to put one on top of the pile.
Mark Nemtsas
Time and Billing Software - Time Tracking Software - Roller Shutters - Roller Blinds -
Baby Books
Time and Billing Software - Time Tracking Software - Roller Shutters - Roller Blinds -
Baby Books
Greetings.
Thanks for both of your feedbacks.
Mnemtsas, your idea sounds interesting but I don't quite get you there.
Could you please explain a bit further?
Let me rephrase what you mentioned.
Do not use auto increment for the PKs, instead use Integer. However, these PKs are similar to auto increment whereby the PKs go like 1,2,3,...etc.
1. Everytime when I'd like to insert a new record, get the Max() of the highest PK, then +1 to the new record.
2. Whenever I delete a record, store that PK into a different table.
3. Next, if I should add a new record anytime, I should give a check in that table to see if there is any unused PK. If yes, use 1 of them. Else, repeat step [1].
Thanks for both of your feedbacks.
Mnemtsas, your idea sounds interesting but I don't quite get you there.
Could you please explain a bit further?
Let me rephrase what you mentioned.
Do not use auto increment for the PKs, instead use Integer. However, these PKs are similar to auto increment whereby the PKs go like 1,2,3,...etc.
1. Everytime when I'd like to insert a new record, get the Max() of the highest PK, then +1 to the new record.
2. Whenever I delete a record, store that PK into a different table.
3. Next, if I should add a new record anytime, I should give a check in that table to see if there is any unused PK. If yes, use 1 of them. Else, repeat step [1].
"Study the past if you would define the future" - Confucius
•
•
•
•
Originally Posted by red_evolve
Greetings.
Thanks for both of your feedbacks.
Mnemtsas, your idea sounds interesting but I don't quite get you there.
Could you please explain a bit further?
Let me rephrase what you mentioned.
Do not use auto increment for the PKs, instead use Integer. However, these PKs are similar to auto increment whereby the PKs go like 1,2,3,...etc.
1. Everytime when I'd like to insert a new record, get the Max() of the highest PK, then +1 to the new record.
2. Whenever I delete a record, store that PK into a different table.
3. Next, if I should add a new record anytime, I should give a check in that table to see if there is any unused PK. If yes, use 1 of them. Else, repeat step [1].
Mark Nemtsas
Time and Billing Software - Time Tracking Software - Roller Shutters - Roller Blinds -
Baby Books
Time and Billing Software - Time Tracking Software - Roller Shutters - Roller Blinds -
Baby Books
Greetings.
Alright, alright! Thanks a lot for the confirmation.
I'll try to work that out, in fact I deem there'll be no problem with coding that out.
One more question that I have to ask here, imagine I have a lookup table in of the columns in a table in Access.
As such in VB, I'd have a combo box. If I do not wish to add the items manually, say
Can I import the items from the access lookup table? If so, could you please give me some clue?
Alright, alright! Thanks a lot for the confirmation.
I'll try to work that out, in fact I deem there'll be no problem with coding that out.
One more question that I have to ask here, imagine I have a lookup table in of the columns in a table in Access.
As such in VB, I'd have a combo box. If I do not wish to add the items manually, say
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
"Study the past if you would define the future" - Confucius
|
Similar Threads
- Need HELP IN returning an auto incriment primary key to zero (PHP)
- Need Help Accessing a Primary key MS Access in SQL to print out informatio. (MS Access and FileMaker Pro)
- HELP! Need someone that Knows SQL to tell Me How to Access a Primary Key (C)
- Need to make program access a data base primary key number entered by user (C)
Other Threads in the Visual Basic 4 / 5 / 6 Forum
- Previous Thread: Adding controls into a ListView
- Next Thread: DVD Collection Program
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Latest Visual Basic 4 / 5 / 6 Posts
- Today's Posts
- Unanswered Threads
Related Forum Features
* 6 429 2007 access activex add age append application basic beginner birth bmp calculator cd cells.find click client code college column component connection connectionproblemusingvb6usingoledb copy creat ctrl+f data database datareport date delete dissertations dissertationthesis dissertationtopic edit error excel excelmacro file filename form hardware header iamthwee image inboxinvb internetfiledownload keypress label listbox listview liveperson login looping machine microsoft movingranges number objectinsert open oracle password prime program prompt range-objects readfile reading record refresh remotesqlserverdatabase report retrieve save search sendbyte sites sort sql sql2008 sqlserver subroutine table tags textbox time timer urldownloadtofile vb vb6 vb6.0 vba visual visualbasic visualbasic6 web window windows
