Adding new record using vba (1 Viewer)

Hi I have a form with various text boxes/combo boxes check boxes representing the fields in a table, on save I want to add the record to the table thus using dmax +1 for the autonumber ensuring no issues with multiple users getting the same number. the following wont work.

Second question is do I need to do a character check on each field to ensure the user has not entered too many characters?

Thanks

Private Sub Command66_Click()
Dim dbNCW6b As DAO.Database
Dim rsttblInvestigations As DAO.Recordset
Set dbNCW6b = CurrentDb
Set rsttbInvestigations = dbNCW6b.OpenRecordset("tblInvestigations")
rsttblInvestigations.AddNew
rsttblInvestigations("No").Value = Me.No
rsttblInvestigations("Due Date").Value = Me.Due_Date
rsttblInvestigations("Notes").Value = Me.Notes
rsttblInvestigations("Date Logged").Value = Me.Date_Logged
rsttblInvestigations("Source").Value = Me.Source
rsttblInvestigations("Investigator").Value = Me.Investigator
rsttblInvestigations("Section").Value = Me!Section
rsttblInvestigations("Method").Value = Me.Method
rsttblInvestigations("Ref1").Value = Me.Ref1
rsttblInvestigations("Ref2").Value = Me.Ref2
rsttblInvestigations("Evidence Only").Value = Me.Evidence_Only
rsttblInvestigations("Issue").Value = Me.Issue
rstrsttblInvestigations.Update
End Sub

This is how I would do it... You need the .Value after your form control names.


Private Sub Command66_Click()
Dim rsttblInvestigations As DAO.Recordset
Set rsttbInvestigations = CurrentDb.OpenRecordset("SELECT * FROM [tblInvestigations]")
rsttblInvestigations.AddNew
rsttblInvestigations![No] = Me.No.Value
rsttblInvestigations![Due Date] = Me.Due_Date.Value
'etc etc....
rstrsttblInvestigations.Update
End Sub
You shouldn't have to check field lengths.

It stops at add new
Saying object variable or with block not set?
Any ideas?
Thanks

Is there any reason why the form is not bound?

Oops! Sorry. I copied your code from the original post, amended parts and pasted it back to my reply. I never noticed that you had a spelling error in your recordset variable names.

If you look carefully the DIM statement and the ADDNEW statements have a variable name, rsttblInvestigations whereas the OpenRecordset statement has the 'L' from the 'rstbL' part, missing, as rsttbInvestigations. For this reason, I always find it more convenient to use slightly shorter variable names.

Here's a bit of working code from my Cash Book database. The code does exactly what you're doing, adds a new record from Text and Combo Boxes on a form, releases variables then closes the form.


Private Sub Command12_Click()
Dim R As Recordset

Set R = CurrentDb.OpenRecordset("SELECT * FROM [cbTrans]")
R.AddNew
R![TrDate] = Me.tDate.Value
R![BID] = Me.cmbBank.Value
R![AID] = Me.cmbAccount.Value
R![cbName] = Me.cName.Value
R![cbDescr] = Me.tDescr.Value
R![cbAmount] = Me.tamount.Value
R![Status] = Me.cmbCleared.Value
R.Update
R.Close
Set R = Nothing
DoCmd.Close
End Sub

Thanks for solving my problem. I will be back with more puzzles later.

vbaInet said:

Is there any reason why the form is not bound?

Click to expand...

I wouldn't use a bound form for this purpose. Just my choice but:

1. The data is entered into a blank form. At the end of the entry process the user may elect to post the data or abort by closing the form without posting.

2. If your database is used in a multiuser environment, which I suspect it is, then the other advantage is that the record is not locked while the user ponders over the data entry but rather locked for a few milliseconds between the recordset.addnew and the recordset.update process.

I know the advantages of using an unbound form over a bound form but there are disadvantages too and you have to know what you're doing to effectively work with unbound forms. I'm not so sure the OP is an expert at this which is why he/she should stick to bound forms. I use unbound forms most times but bound forms have their place.

TheTerminator said:

I wouldn't use a bound form for this purpose. Just my choice but:

1. The data is entered into a blank form. At the end of the entry process the user may elect to post the data or abort by closing the form without posting.

Click to expand...

We're not sure the state of affairs here. The OP didn't mention the purpose of the form.

TheTerminator said:

2. If your database is used in a multiuser environment, which I suspect it is, then the other advantage is that the record is not locked while the user ponders over the data entry but rather locked for a few milliseconds between the recordset.addnew and the recordset.update process.

Click to expand...

Again, this is down to how well the form was designed. Access handles record locks fairly well if designed properly.

Exactly, I have a multi-user environment and need the record I'd to be continuous and unique that is why I have opted for unbound plus vba.

I beg to differ, but the original post read as, "on save I want to add the record to the table", and in his sample code, he had the following statements:
Private sub Commandxxx_click()
Rsttblinvestigations.addnew

Sounds pretty much like he was using an unbound form, to me.

Yes I know it's an unbound form, but I think I missed your point in 1. I thought you were making a point there.