This should be easy, right??

[Clamato]

Hi everyone,

I'm having the hardest time with an issue that you would think to be easy to do! But, being an access noob, I can't seem to get it to work. Access 2000.

What I have is a tab within a form used to basically input records into a table. It's set up now with one text box where the user will enter the clients name (txt_client), a field for the clients TIN (txt_TIN), a combox with the document type (cmb_type). I have a button linked to a module that brings up an explorer window for the user to select the file they saved, then when selected the path displays in a hidden text box (txt_loc). I'd like to input all of the information the user enters into the corresponding table (tbl_doc).

I'd also like to format the text box displaying the path with a format. What I mean is when the path is displayed in txt_loc, it shows C:\path\path\document.pdf for example, the drive the documents are on is a shared drive on a server. So I'd like to replace C:\ with the actual drive name on the server, for example replace C:\ with \\mappeddrive\name\path\document.pdf. The column that corresponds with the txt_loc is the primary key in the table if that makes a difference.

I've tried creating a recordet, using a connection and tried with just a update sql statement in the event procedure of the button. I can not get the thing to work for the life of me. Any help would be GREATLY appreciated!!

[Scott Price]

Is there a really really good reason why you have made a potentially non-unique value the primary key of this table? Because, unless there's a really really good reason, it's a bad idea!

Mis-typed document names, paths, duplicate documents, etc can all wreak havoc by causing unhandled errors, frustrated users, and potential data corruption.

I would highly suggest creating a separate field (column) to use as the primary key value. Autonumber data type is the standard for such a field, although you don't necessarily have to use it.

Would this also do away with your need to refer to the path in a hidden text box? I use hidden text boxes with the primary key value mainly to refer to the current record when using a form in continuous view. Is this what you are attempting?

Regards,
Scott

[Clamato]

Is there a really really good reason why you have made a potentially non-unique value the primary key of this table? Because, unless there's a really really good reason, it's a bad idea!

Mis-typed document names, paths, duplicate documents, etc can all wreak havoc by causing unhandled errors, frustrated users, and potential data corruption.

I would highly suggest creating a separate field (column) to use as the primary key value. Autonumber data type is the standard for such a field, although you don't necessarily have to use it.

Would this also do away with your need to refer to the path in a hidden text box? I use hidden text boxes with the primary key value mainly to refer to the current record when using a form in continuous view. Is this what you are attempting?

Regards,
Scott

Hi Scott,
No, there honestly isn't any reason why I made the path the primary key. I guess I was thinking I wanted that to be unique to keep people from submitting multiple records. Before, I didn't have a primary key at all, an autonumber would work just fine. I will use that instead. :)

I'm hiding the path in a hidden text box only so the form looks more streamline to the users. I'm ultimately trying to get the path into the appropriate record of the table, I just couldn't figure out how to transfer that information directly from the open window explorer to the table. I've had success transfering a path from the table to a hidden text box in an opposite manner, so I figured it would be easier to do the opposite to achieve this goal.

Thanks for the help!

[Scott Price]

I see, I think :-)

Here is a link that discusses the open file dialog box a bit. You need to access the .SelectedItems collection of the dialog box to capture the path it contains. Let me know if you need more help!

Regards,
Scott

[Clamato]

I see, I think :-)

Here is a link that discusses the open file dialog box a bit. You need to access the .SelectedItems collection of the dialog box to capture the path it contains. Let me know if you need more help!

Regards,
Scott

Thanks again Scott,

I think I understand the code for the dialog box, I currently have it where the selected file displays the path in the text file like we discussed earlier, if the code you included may be a more streamline way to accomplish this I'll definetely make that change.

The main issue I was having was actually inputing and formating that information into a table. I don't have any experience with Access and have been having a tough time finding tutorials on how to do this. I'd like to input all of the information the user inputs in the other text boxes into a corresponding column in the table once the user selects the file from the dialog. And of course include the path they select in it's record after it's been modified with the share name as opposed to the drive letter. I hope I'm explaining that right, thanks again!

[Scott Price]

You can capture the .SelectedItems value in a string variable, then under the AfterUpdate event of whatever control you are using to enter the data, set the text box control value to the variable.

As for getting that into a table, you will bind your controls to a query (preferable) or table (acceptable as long as you only need fields on the form that correspond exactly to the table). Any information entered into the controls will be entered also into the respective fields in the underlying table.

Getting the idea?

Regards,
Scott

[Clamato]

You can capture the .SelectedItems value in a string variable, then under the AfterUpdate event of whatever control you are using to enter the data, set the text box control value to the variable.

As for getting that into a table, you will bind your controls to a query (preferable) or table (acceptable as long as you only need fields on the form that correspond exactly to the table). Any information entered into the controls will be entered also into the respective fields in the underlying table.

Getting the idea?

Regards,
Scott

Thanks Scott,

I definetely understand the concept, it's just putting it into code that's the problem.

I attempted to create an append query, but was running into trouble having the query take the string from the certain text box and place into the appropriate column in the table. I would also like the table update to not really involve the user except for them to perform the action, with the query it would ask if they were sure they'd like to perform the action, which didn't work anyways, and I'd like to avoid that query message box if possible.

I also tried to create a sql statement in the OnClick event of the button, but I couldn't seem to figure that out either. Any idea on where there may be a tutorial on how to do that? I'd like to learn on my own, it's just been tough finding information on this subject.

When you say bind the controls to the table directly, but the fields would need to correspond exactly to the table. Do you mean they would need to be in the same order as the table, or do they need to be named the same, or am I missing the point?


Thanks for the help!

[rsmccli]

I think you just need set your form's 'recordsource' property to your table/query, then set each of your textboxes' 'controlsource' property to their respective fields in the table/query. That way you would just be putting the data right into the table instead of doing an update query with the values of the textboxes or whatever you were doing.

Hope it was this simple.

[Scott Price]

When I refer to 'binding' a control to a particular field in your form I mean setting the Record Source for the Form to a query or table, then setting the Control Source of the control to a field in the query or table.

Open your form in design view. Double click on the small square in the upper left hand corner of the form's design view window. This will bring up the Properties dialog box for the form. On the Data tab of the form you will find a combo box with the possible options you can select as Record Source. Choose the query/table you wish to view data from and add data to. The right click on each control (text box, etc.) of your form and also on the Data tab, using the Control Source combo box, choose the field in the query/table that you wish to hold the data entered into that control.

As for the messages that appear when using an action query, those messages can be turned off in at least two different ways. However, to avoid confusing the issue, we'll talk about that later.

Now then, the path to the file that you wish to change from the default C: location to the network server location. Can you post the code that you are currently using to capture the path?

Also, please post the structure of your table. Here is an example of how to post this table 'meta-data':
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Regards,
Scott

[Clamato]

When I refer to 'binding' a control to a particular field in your form I mean setting the Record Source for the Form to a query or table, then setting the Control Source of the control to a field in the query or table.

Open your form in design view. Double click on the small square in the upper left hand corner of the form's design view window. This will bring up the Properties dialog box for the form. On the Data tab of the form you will find a combo box with the possible options you can select as Record Source. Choose the query/table you wish to view data from and add data to. The right click on each control (text box, etc.) of your form and also on the Data tab, using the Control Source combo box, choose the field in the query/table that you wish to hold the data entered into that control.

As for the messages that appear when using an action query, those messages can be turned off in at least two different ways. However, to avoid confusing the issue, we'll talk about that later.

Now then, the path to the file that you wish to change from the default C: location to the network server location. Can you post the code that you are currently using to capture the path?

Also, please post the structure of your table. Here is an example of how to post this table 'meta-data':
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Regards,
Scott

Thanks Scott,

Ok, I understand the Record Source and Control Source now, I've made that change to my form and the controls I'd like to use.

As for the code that's being used for the dialog box, I used this from another source attempting to obtain the same goal as I. It's listed below

Button on the form:

 
Private Sub btn_load_Click()
Me!Txt_loc = LaunchCD(Me)
 
End Sub
 
 
 

Module:

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
 
Private Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type
 
Function LaunchCD(strform As Form) As String
    Dim OpenFile As OPENFILENAME
    Dim lReturn As Long
    Dim sFilter As String
    OpenFile.lStructSize = Len(OpenFile)
    OpenFile.hwndOwner = strform.Hwnd
    sFilter = "All Files (*.*)" & Chr(0) & "*.*" & Chr(0)
    OpenFile.lpstrFilter = sFilter
    OpenFile.nFilterIndex = 1
    OpenFile.lpstrFile = String(257, 0)
    OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
    OpenFile.lpstrFileTitle = OpenFile.lpstrFile
    OpenFile.nMaxFileTitle = OpenFile.nMaxFile
    OpenFile.lpstrInitialDir = "C:\"
    OpenFile.lpstrTitle = "Select a file"
    OpenFile.flags = 0
    lReturn = GetOpenFileName(OpenFile)
        If lReturn = 0 Then
            MsgBox "A file was not selected!", vbInformation, _
              "Select a file"
         Else
            LaunchCD = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
         End If
End Function
 
 

The dialog box displays the path as we discussed in a C:\folder\folder\document.pdf format. I'd like it to show for example \\server\share\folder\folder\document.pdf as the drive letters change quite a bit here. :)


Table Name=tbl_doc

Field; Type; IndexInfo
AutoNumber; AutoNumber; PK
doc_type; Text
client_name; Text
client_ssn; Text
doc_location; Memo
cm_name; Text
 

Thank you!!!!

[Scott Price]

You are setting the initial file location in line 40 of your code to the C: directory. If you change this to the server drive path, this should make the display start at that server directory.

Now that you have the controls on your form bound to the proper fields, it will be just a matter of saving the file path chosen in your dialog box into a variable that is exposed by your function. Let me get back to you in a second.

Regards,
Scott

[Clamato]

You are setting the initial file location in line 40 of your code to the C: directory. If you change this to the server drive path, this should make the display start at that server directory.

Regards,
Scott

Holy crap! Wow, I feel like an idiot. I should have realized that. DUH!

[Scott Price]

After looking over your code again, and copying it into my test database, I realized that it isn't going to work! At least it doesn't compile for me in A2003 :-(

Happily, our own ADezii in this thread has posted clear instructions in how to get this to work. Rather than duplicate his post, I'll let you read through the thread, and then come back to me here with any questions you might have!

Regards and Good Luck!
Scott

[Clamato]

After looking over your code again, and copying it into my test database, I realized that it isn't going to work! At least it doesn't compile for me in A2003 :-(

Happily, our own ADezii in this thread has posted clear instructions in how to get this to work. Rather than duplicate his post, I'll let you read through the thread, and then come back to me here with any questions you might have!

Regards and Good Luck!
Scott

Thanks Scott,

I am using access2000, I wonder if that's why it may not be working for you. The dialog box seems to be working perfect for me at least. :/ You stated earlier there is a way to avoid the "confirmation" prompt when an append query is run. Is there a flag I can input, or how would I accomplish tha?. Also, from a SQL statement standpoint, how would I create a statement that would append a string that was input into a text box? I think that may be my last step to get this thing working! :)

[Scott Price]

If your text box is properly 'bound' to the field in your table, there should be no need for an action query to update the field.

In your dialog box code you'll just need to set the text box value to the variable you have assigned to collect the path. Like this:

Me.TextBox1 = varSelectedItems

Then any changes to the variable get written to the text box, which when you move to another record or in some other way save the current record, gets written to the table.

For general information, this code turns off the warnings messages for action queries:

Application.SetOption "Confirm Action Queries", False

Don't forget to set the warnings back on again with this code:

Application.SetOption "Confirm Action Queries", True

Regards,
Scott

[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