Generate Primary Key in VB / Delete Record Status

[kampf]

Ok, yes this is an assignment. Yes It's due tomorrow, and yes I have spent countless hours trying to do this, my head is fried I start work in less than 6 hours and it's due in approximately 17hours time.

Main problems:

- I need to generate a Primary Key in VB for a MS Access database (im not allowed to use the auto-increment feature). This PK is going to be a number that increments +1 for each record added.

- I need to be able to delete records from the database. I view a record in VB (from the database) if the record status (I have a field in the database called AccStatus) = Yes. So when i delete a record I want the status = No, so it won't show up when I view all records, yet it will still exist in the database.

- Database connections must be carried out using ADO

Im after a quick solution (yes I know this totally goes agains the board rules but hey, its worth a try). Im also willing to pay whoever comes up with the full solution via paypal (a reasonable amount).

Here is what I have for generating the AccNum (primary key), pretty much pseudocode

On cmdAdd Click:

'Auto generate AccNum when adding new Account
Select LastAccNum from tblsystem (tblSystem is the table name that stores the last AccNum stored in another table called tblAccounts)
txtAccNum.text = LastAccNum + 1

I really need to get some sleep before work, so I'll check it in a few hours and if somebody is interested then please email me

[e-mail]

and I can send you all the code I have and the MS Access Database, which will make solving this problem a helluva lot easier.

Im sorry I had to stoop this low

/can't believe how pathetic i've become. :/

p.s Just Noticed can add attachments - i've included it all as a zip.

[plusplus]

Are you asking about your following code?

pstLastAccNum = "Select LastAccNum " & _
"From tblSystem " & _
"Where txtAccNum.Text = prsLastAccNum!LastAccNum + 1 & " '"
prsAccNum.Open pstLastAccNum, gcnTrueBlue, adOpenStatic, adLockBatchOptimistic, adCmdText

Do you want to add a record whose account number is lastaccnum + 1? Do the following

'in general declaration add the following
Dim lastone As Long 'variable to hold last acc num

'in cmdadd
'change pstLastAccNum as integer to
Dim pstLastAccNum As String
'change the sql statement to the following
pstLastAccNum = "Select LastAccNum " & _
"From tblSystem "

prsAccNum.Open pstLastAccNum, gcnTrueBlue, adOpenStatic, adLockBatchOptimistic, adCmdText

lastone = prsAccNum!LastAccNum 'put lastacc number into lastone
lastone = lastone + 1 'and add one to it to get new lastacc
txtAccNum.Text = lastone 'put the new acc number in textbox

'in your cmdsave change !AccNum = txtAccnum.text to
!AccNum = lastone 'the variable that holds the new accnum, put the new accnum in tblaccounts

[plusplus]

The above code was for your cmdadd
your cmddelete should be changed to something like the following(not sure if its complete)
compare your cmdDelete with this one

Private Sub cmdDelete_Click()

Dim pstDeleteAccountSQL As String
Dim prsDeleteAccount As New ADODB.Recordset

If MsgBox("Are you sure you want to remove this Account?", vbYesNo) = vbYes Then
'** Open a recordset that contains the selected Account to be deleted
'change tblbooks to tblAccounts
pstDeleteAccountSQL = "Select * " & _
"From tblAccounts " & _
"Where AccNum = '" & txtAccNum.Text & "'"

prsDeleteAccount.Open pstDeleteAccountSQL, gcnTrueBlue, adOpenStatic, adLockBatchOptimistic, adCmdText
Dim what As String
what = prsDeleteAccount!AccStatus
'** need to make it change AccStatus = false
'leave the following away, cause you don't want to delete it
'prsDeleteAccount.Delete
'instead just change status
prsDeleteAccount!AccStatus = False
prsDeleteAccount.Update 'don't forget this line
what = prsDeleteAccount!AccStatus
prsDeleteAccount.Close
Set prsDeleteAccount = Nothing
'I didn't check what the following two lines do
'mrsAccounts.Requery
Call DisplayData
End If
'************************************************************************************
'*make sure when you display data to check if AccountStatus = true, only then display
'**************************************************************************************
End Sub