Save data from textbox to table

[oOGrayWolfOo]

Essentially I am trying to duplicate the "comment" command button from the "Issues Web Database" (located here office.microsoft.com/en-us/te...010375249.aspx

Only, this database is a Web Database and the database that I am updating is not. Not sure if there are many differences, but I have not been able to replicate this function.

What I have are:
a Comments table
a Form
a subForm

tblComments - table that tracks the comments separately from the main table
ID - Autonumber - PK
MainID - Number - this is the record that the comment is associated to
CommentDate - Date/Time - the "now()" time that the comment was entered
Comment - memo - the text field for the comments
UserID - number - this is the record number for the user who entered the comment

frmMain

Dozens of Fields plus the following 2 below (which are key to my issue)
txtAddComment - unbound - this is a free form text field
cmdAddaComment - Command Buttom - this button, when pressed, should update the following:
tblComments.MainID with the record we are currently viewing in frmMain
tblComments.Comments with the information from txtAddComment
tblComments.CommentDate with Now()
tblComments.UserID with GetNetworkUserName()(a module I have)
and clear the txtAddComment field to be ready for the new comment.

sbfrmComments
contains a Report that pulls the related tblComments for the record currently being viewed frmMain.


I have also attached my Database View attachment Issue Test.zip

Does anyone have any suggestions or require any further information?

 

[oOGrayWolfOo]

I Solved this issue.
A simple Append Query a little VB script to turn off the warning pop-ups and then turn them back on and Voila.

 

[bockmj18]

hopefully this isn't too out of date, but I'm trying to do the exact same thing but I can't get it working properly. Anyway you could help me out with how you solved yours? When I run the Append Query, it's trying to append ALL records instead of just the data typed into the unbound textbox.

I'm slowly learning Access (took a class on it in college), and I'm starting to learn VBA and SQL, but still not very proficient in it. Along with that, it doesn't help that I learned using Access 2007/2010 and I'm building this thing in Access 2000...

 

[oOGrayWolfOo]

Hi bockmj18.

I'll do my best, but I did this 3 months ago and I haven't touched it since, so bear with me as I am not a technical writer and some of this might be confusing. Just let me know if you have any questions after the fact. Some of the fields may not be relevant to you, but I wanted to ensure that I did not miss something.

Here are the components

Unbound text box called txtAddComment on Detail section of a form named Main

Command Button called cmdAddaComment and in the On Click I have the following event procedure
Private Sub cmdAddaComment_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAddCommentBtn", acViewNormal
Me.[txtAddComment] = Null
sbfrmComments.Requery
DoCmd.SetWarnings True

End Sub

table called Comments with 4 fields
ID - which is my primary key that is just an autonumber
MainID
CommentDate
Comment
UserID

query called qryComments set up like So:
Field1 - Comments.*
Table - Comments

Field2 - Login
Table - Users

Field3 - CommentDate
Table - Comments

report called rptComments which pulls from qryComments (above) and displays all the fields from the above query.

subform tab called sbfrmComments that houses the report called rptComments.

an appendquery called qryAddCommentBtn - these are the 4 fields I am grabbing and appending to my
Field 1 - MainID: [Forms]![Main].[MainID]
Append to - MainID

Field 2 - CommentDate: Now()
Append to - CommentDate

Field 3 - Comment: [Forms]![Main].[txtAddComment]
Append to - Comment

Field 4 - UserID: [Forms]![Main].[textboxGetUserID]
Append to - UserID

The key was to make sure you used an "append Query". just google how to create an append query for your version of Access.

I really hope this helps. It took me a little bit of tweaking to get this just right. Let me kniow if you have any further questions.

 

[bockmj18]

Thank You for the help! It's working to a point, but there's still something wrong with it.

I added the VBA to the button and the Append Query is working as it's supposed to now, but when I click on it and the Comments subform requeries/refreshes, it has appended 444 new records (which is the number of Main ID's I currently have in the database and also added to the Comments table). I'm not sure how to get it to append just the one new record from the unbound textbox.

My guess is it's something contained within the Append Query as it looks like it is cycling the same data that was typed into "txtAddComment"

Any ideas?

 

[oOGrayWolfOo]

Hmmm ... I am thinking it may have to do with relationships. I may be wrong, but I think I recall having to tweak the relationship. If you are ok with it, I can send you a copy of my DB (1.5Mb unzipped) if you are able to send me your email. Probably best to PM your email to me.

 

[bockmj18]

Sure, I can do that. I did some more digging online and I'm getting mixed answers saying that adding criteria to the Append Query could remedy it, along with your suggestion to look at table/query relationships, as well as modifying the SQL

 

[oOGrayWolfOo]

I can't seem to find a way to PM a contact on this forum. Looks like it may be disabled. If you use skype you can just look for oograywolfoo and you'll find me .

Sure, it very well may be as you mentioned. I know that in my query there was no criteria and I know for sure that I did not write any of my queries in SQL.

 

[bockmj18]

I think you can upload it to the thread unless you have protected info that you don't want out to the public

 

[oOGrayWolfOo]

Take a look at this. It has some dummy data in there. Be sure to Hold SHIFT when you open it so you bypass the password login.

 

[bockmj18]

Thanks for the upload, but I can't open it as I'm working off of Access 2000.

HOWEVER, I did fix it with some SQL.
Because the Append Query wasn't pointing to a specfic record for it to Append to, it was cycling. All I needed to do was to tell it where to write using the WHERE

My Append Query ends up looking like this:

Field 1-DataF:Forms!DataF.ID
Append To:
ID

Field 2-Comments:Forms!DataF.txtAddComment
Append To: Comments

Field 3-Date/Time: Now()
Append To: Date/Time

Field 4-[Forms]![DataF].[ID]
Criteria: [ID]

In SQL it looks like this:

INSERT INTO Notes ( ID, Comments, [Date/Time] )
SELECT Forms!DataF.ID AS DataF, Forms!DataF.txtAddComment AS Comments, Now() AS [Date/Time]
FROM Notes
WHERE ((([Forms]![DataF].[ID])=[ID]));

The weird this is, I've been trying to get the WHERE to work and I would always get an expression error about a semicolon at the end of the SQL statement (and there was a semicolon present), but now it works. :banghead:

I'll run a few more tests on it to see if I can break it in any other way.

Thanks for all your help!

 

[oOGrayWolfOo]

My pleasure. Glad you got it working ... and you may not want to hit your head against that wall too many times or you might forget what you just did .

All the best.

 

[awse]

Hi
I see that my problem is similar to yours , wish u can help with this please,
I have a form named "Main frame", it contains a text box called "password", i want the password entered by a user in the form saved in a field called "voter" in "password" table
and for your knowledge the main form does not have a table
hope u help in this
thank u all

 

[awse]

waiting for your reply

 

[John Big Booty]

waiting for your reply

Dude, I shouldn't hold my breath if I were you, as neither poster has been on this forum for over six months.

 

[awse]

I already solved the problem, thanx