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\do cument.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\n ame\path\docume nt.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

[i]Field; Type; IndexInfo[/i]
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

[i]Field; Type; IndexInfo[/i]
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\folde r\document.pdf format. I'd like it to show for example \\server\share\ folder\folder\d ocument.pdf as the drive letters change quite a bit here. :)


Table Name=tbl_doc

[i]Field; Type; IndexInfo[/i]
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 "confirmati on" 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