This should be easy, right??

[Clamato]

Thank you Scott,

Well, I'm still having trouble with this. It appears the other person that had this before me somehow disabled the ability to move through records from the form. Any records that needed to be input are done through code directly and any records that need to be viewed run from query's.

I've bound the text boxes that I need to input into the table, and have an append query that runs once I click the button and the user selects the path it runs the query to append. Well....it's not working for some reason. I left the confirm messages to just make sure everything's savvy, and it doesn't append any rows. I'm assuming it has to do with my query, what do you think?

INSERT INTO tbl_doc ( cm_name, client_ssn, client_name, doc_location )
SELECT tbl_doc.cm_name, tbl_doc.client_ssn, tbl_doc.client_name, tbl_doc.doc_location
FROM tbl_doc
WHERE (((tbl_doc.cm_name)=[Forms]![frm_na]![txt_cm]) AND ((tbl_doc.client_ssn)=[Forms]![frm_na]![txt_TIN]) AND ((tbl_doc.client_name)=[Forms]![frm_na]![txt_client]) AND ((tbl_doc.doc_location)=[Forms]![frm_na]![cmb_type]));

[Clamato]

Ok, i think I got it working! I'm going to work out any bugs, if I have any other questions I'll come back. Thanks so much again!

[Scott Price]

I have no way of doing the testing on this, so you'll have to do that yourself :-)

Create a new query in design view, and paste the SQL you have (just the Select part and onward) into it. Run this separate query to see if it is returning any results. If not, you'll have to fine-tune the query until you get the results you want.

Regards,
Scott

[Clamato]

Hey Scott,

I thought it was working, but boy was I wrong. I'm not sure what's going on, but when the append query runs it takes whatever records are in the table and duplicates them. So it took the first one, then when I tried again from the form it updated it twice, then four times, then 8, 16 and so on...... From a query standpoint..... if I had a textbox in a form and I wanted to add the text that's input in that box to a new record in the table, how would I do that?

I'm not able to just bind it and input the information and scroll onto the next record, the person that had this project before me removed the scroll option on the bottom. So I'm stuck trying to figure out how to input the records from a button I've created on the form. I've tried creating a query through the design view, and pointing the criteria to the text-box on the form, but when it runs all it does is duplicate everything. I do have the text-box's control source to the column I want the string to append to. Am I crazy?

[Clamato]

I've tried this in the event for the button, but am getting syntax errors. I saw this on another forum, they were trying to accomplish the same thing as I, feel free to laugh at me if this is rediculously wrong, Access is soo new to me :)

Private Sub btn_load_Click()
CurrentDb.Execute "Insert into tbl_doc (client_ssn) Values('" & Me.txt_TIN& "');"
End Sub

[Scott Price]

In form design view, open the properties dialog box. On the Data tab, make sure Allow Additions and Data Entry are both set to Yes. Then on the All tab, make sure the Scroll bars are set to Both, the Record Selectors are set to Yes and the Navigation buttons are set to Yes.

Let me know if this works.

There is absolutely no reason that I can think of for you to need an update query for what you are trying to do.

Regards,
Scott

[Clamato]

In form design view, open the properties dialog box. On the Data tab, make sure Allow Additions and Data Entry are both set to Yes. Then on the All tab, make sure the Scroll bars are set to Both, the Record Selectors are set to Yes and the Navigation buttons are set to Yes.

Let me know if this works.

There is absolutely no reason that I can think of for you to need an update query for what you are trying to do.

Regards,
Scott

Ok, I see. Well, I don't really think I'd like to have users update records like that. I know it's the simplest way, but I don't think the users that use this will be able to understand how this works. I'm trying to stay more object oriented like the rest of the portions of the form, so I'd like to remain using a button to update records. I'll keep searching and looking around. Thanks again

[Scott Price]

I'm a little confused now :-)

Whether the user inputs their information (file path, etc) indirectly through an action query or directly by typing into the text box on the form, they are still inputting the information, no?

If you wish to validate the information that they enter, there are other ways to do this as well.

The real key to allowing what you want is the Data Entry set to Yes for this form. You can still make sure that the file path is valid in the way you are doing (capturing the path from the file open dialog box, and plug it into the hidden text box), you can still restrict their access to vital information (by 'locking' certain controls if needed). If you don't want them to navigate to other records, you can leave the navigation buttons setting to No, etc. In this case, you'll need to manually (in code) save the changes that the user enters by inserting in the forms On Dirty event something like this:

If Me.Dirty = True Then
   Me.Dirty = False
End If

However, as I said before, there is no reason to need an update query for what you are doing!

And, no, no one is laughing at you for your honest attempts to make this work :-) We all heartily applaud people like you, who work hard at understanding what is happening as well as do research to find out how to do what you need to do!

Kind regards,
Scott

[Clamato]

I'm a little confused now :-)

Whether the user inputs their information (file path, etc) indirectly through an action query or directly by typing into the text box on the form, they are still inputting the information, no?

If you wish to validate the information that they enter, there are other ways to do this as well.

The real key to allowing what you want is the Data Entry set to Yes for this form. You can still make sure that the file path is valid in the way you are doing (capturing the path from the file open dialog box, and plug it into the hidden text box), you can still restrict their access to vital information (by 'locking' certain controls if needed). If you don't want them to navigate to other records, you can leave the navigation buttons setting to No, etc. In this case, you'll need to manually (in code) save the changes that the user enters by inserting in the forms On Dirty event something like this:

If Me.Dirty = True Then
   Me.Dirty = False
End If

However, as I said before, there is no reason to need an update query for what you are doing!

And, no, no one is laughing at you for your honest attempts to make this work :-) We all heartily applaud people like you, who work hard at understanding what is happening as well as do research to find out how to do what you need to do!

Kind regards,
Scott

Thanks Scott,

I agree about the query, I thought that may be the easiest way to update the records, either way you're right, they're entering the data to input. The on dirty event seems to work. However, this adds the record once the form is closed right? Or does it add the record when a change in the form occurs? I can't seem to tell. They usually will be adding one record at a time, but I would like to be able to add them without closing the form, such as with a button, if that's how they are updated.


Thanks for the encouragement, I sure do need it!

[Scott Price]

Not a problem! We all need positive feedback :-)

The On Dirty event happens whenever there is any change to the form or it's contents: i.e. the form becomes 'dirty'. You can place the Me.Dirty = False code behind the button if you want... Then the users can simply click the button marked "Save" to save their changes.

Good luck!

Regards,
Scott

[Clamato]

Beautiful, ok I think it's finally working. I didn't realize this, but I found in the wizard the add new record button. So I just used the code from that and added it to my existing button that brings up the dialog box. And with the OnDirty event everything gets saved! Man, after looking back it seemed like the solution was easy, just took me a bit to figure it out.

Thanks so much for everything again Scott, I appreciate it!

[Scott Price]

Not a problem! Glad it's working for you :-)

Good luck with the rest of your app!

Regards,
Scott