Split 2010 Database update on click (1 Viewer)

[FreonIceMan]

I have created a database using Access 2010 32 bit. It worked fine until I split the database. The problem was that I had a macro referencing a data Macro attached to a table in the database.

Now I have one function that will not work the way I want to, I have looked around the internet to fix the problem and this is far as I have gotten.

What I was to to happen is when I click my save button is my Comments table has an entry added with the current record's ID "ContactID", the comment that is entered in "txtAddComment", and the current date and time. After that is entered I would like the "txtAddComment" to clear and the form to update or specifically the sub form "sfrComments" to update to show the new entry. I would also like it to not make an entry if there is nothing in "txtAddComment".

So far I have been able to get the entry to happen. Below is my code any help would be much appreciated. I have no experience in VBA other than what I figured out from the internet.

Private Sub cmdSave_Click()
Dim dbNPVdB As DAO.Database
Dim rstComments As DAO.Recordset

Set dbNPVdB = CurrentDb
Set rstComments = dbNPVdB.OpenRecordset("Comments")


rstComments.AddNew
rstComments("CommentDate").Value = Now()
rstComments("Comment").Value = [txtAddComment]
rstComments("ContactID").Value = [txtID]
rstComments.Update

End Sub

 

[HiTechCoach]

Try this:

Private Sub cmdSave_Click()

Dim rstComments As DAO.Recordset

Set rstComments = CurrentDb.OpenRecordset("Comments")

rstComments.AddNew
rstComments("CommentDate").Value = Now()
rstComments("Comment").Value = Me.[txtAddComment]
rstComments("ContactID").Value = Me.[txtID]
rstComments.Update

' recordset  housekeeping
rstComments.Close
Set rstComments = Nothing

' requery sub form
Me.sfrComments.form.Requery

End Sub

 

[FreonIceMan]

I get "Run-time error '2467': The expression you entered refers to an object that is closed or doesn't exist."

Then when I hit Debug Me.sfrComments.Form.Requery is highlighted.

 

[HiTechCoach]

I was guessing that there was a sub form named sfrComments on the form with the command button.

Verify the name of the sub form control (not the name of the form displayed in the sub form control). I assumed the name was sfrComments. You may need to change the name in the code.

 

[FreonIceMan]

That is strange. That is the name of my subform.

Also the housekeeping portion of the code is not doing what it's supposed to.

 

[HiTechCoach]

That is strange. That is the name of my subform.

Try this:

Me.sfrComments.Requery

Also the housekeeping portion of the code is not doing what it's supposed to.

Hmm ... What indicates that it is not working? Are you getting an error message?

How are you determining/testing to know the the record rstComments is still open?

 

[FreonIceMan]

Ok changing it to Me.sfrComments.Requery work in now updates.

The problem with the house keeping part is the text in the text box is still there.

Also any ideas on how not to make an entry when the text box is empty?

 

[HiTechCoach]

The problem with the house keeping part is the text in the text box is still there.

Ah ... the housekeeping is for the recordset only. You were not closing it and destroying the object (=Nothing)

To clear the textbox you will need to add code to set the text box to Null

For example:

Me.[txtID] = Null

Also any ideas on how not to make an entry when the text box is empty?

Sure. Check to be sure it has a value before the insert.


For example:

If Len(Trim(Nz(Me.[txtID],"")))  = 0  then Exit Sub

 

[FreonIceMan]

Thanks. It works great!

This is my final code.

Private Sub cmdSave_Click()

' If text box Null
If Len(Trim(Nz(Me.[txtAddComment], ""))) = 0 Then Exit Sub


Dim rstComments As DAO.Recordset

Set rstComments = CurrentDb.OpenRecordset("Comments")

rstComments.AddNew
rstComments("CommentDate").Value = Now()
rstComments("Comment").Value = Me.[txtAddComment]
rstComments("ContactID").Value = Me.[txtID]
rstComments.Update

' recordset housekeeping
rstComments.Close
Set rstComments = Nothing

' requery sub form
Me.sfrComments.Requery


' clear the text box
Me.[txtAddComment] = Null

End Sub

 

[HiTechCoach]

You're welcome! Glad I could assist.

Great job getting the code put together.


Cheers

 

[sanatar]

I am glad to have found this post and this code, it seems to solve a similar problem that I have. By looking at your variable names and the task you were looking to solve, I am guessing you were using the Contact web database template and I am trying to solve the same problem you listed here.

I am a super noob at VBA and I don't know where I put that code you list here and how to get the button to initiate that code. Any help would be greatly appreciated.

Cheers,
John

 

[HiTechCoach]

... I am guessing you were using the Contact web database template ...

John,

Web databases do not allow VBA code. Only macros. This thread is about issues after splitting a standard (non web) database.

If you are going to use a Web database then you will need to learn to use Macros not VBA code. You will not be able to use this VBA code in a web database.

 

[sanatar]

I apologize for that, I was assuming this was the same template I am working with since all the fields and tables have the same names. Regardless, I am not going to be using this database for web and will recreate as a non web database.

I am still hoping to get some tips on how to implement this code.

Cheers,
John

 

[HiTechCoach]

I apologize for that, I was assuming this was the same template I am working with since all the fields and tables have the same names. Regardless, I am not going to be using this database for web and will recreate as a non web database.

I am still hoping to get some tips on how to implement this code.

Cheers,
John

John,

The tables may be the same for both the web database and the non web database template. If you do not want to recreate it, look at the non web database version of the same template.

Note: You can use any of the templates from Microsoft that are for the versions 2000, 2002/2003, 2007, and 2010 with Access 2010.

If you would like help with the code the I would recommend starting a new thread

 

[FreonIceMan]

John,

To get the code to work I had created a button. I then went to the property sheet, clicked the event tab, then click into the "On Click" box, click the three dos "..." and select code builder and place the code there.