read and update single record in table via VBA

[tburn]

Hi all,
I'm, sure this has been asked before but i cant fnd any info on it so here goes...

I have developed a system to back up or export the contents of the tables in my data base to delimited text files with the destination being chosen via a folder browser,

However,
What i would like to do is store a location in a table and then default to that unless the user wishes to change it.

I was thinking of one table with one record of one feild called Location that stores the value. and then use VBA to read this and edit it if nescesary.

Could anyone provide the code to this please?

Many thanks in advance,
Tommy

 

[mdlueck]

In my apps, I usually have a cfg table that has string based key/value pairs. Much like an INI file w/o the [section] support.

So I would envision in that table have a key = DefaultSaveDirectory and the value = C:\Some\Path

Perhaps use the MS Office file dialog support in Access to allow navigation from the default starting spot to where they ultimately want the file to end up.

I have in my app I am currently working on support to import CSV files which are parts lists. Code as follows:

  Dim fDialog As Office.FileDialog
  Dim strFilename As String

  'Set up the File Dialog
  Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
  With fDialog
    .AllowMultiSelect = False
    .title = "Import Parts List - Please select one file to import into this product."
    .Filters.Clear
    .Filters.Add "Text Files", "*.csv"
    If .Show = True Then
      'Receive back which file was selected
      strFilename = .SelectedItems.Item(1)
    Else
      'Dialog cancled, so exit this method
      modparts_importwizard_ReadImportFile = False
      GoTo Exit_modparts_importwizard_ReadImportFile
    End If
  End With

Note: There are several variations of the dialog: dir selection, file creation, etc... this code happens to use the "file open" flavor.

 

[tburn]

Thanks for your reply,
I'm afraid it does not quite answer the question, however.

What i'm after is simply the code or some explination of how to get a string from a record in a table into a variable in vba.

I am using:

   Set Location = CurrentDb.OpenRecordset("SaveLoc")
          '(Save loc is the table name)
Location.Move 0
 
   Location.Edit
   Location("Location").Value = place
 'place is the variable i am writing to the table from
 
   Location.update

Which works fine to save to table but cant work out how to read the data back into a variable.
Thanks again,
Tommy

 

[mdlueck]

but cant work out how to read the data back into a variable.

For that, please see...

"How to: Move Through a DAO Recordset"
http://msdn.microsoft.com/en-us/library/bb243789(v=office.12).aspx

 

[tburn]

Thanks, but isn't there a way to just specify a single record without all that code?
something similar to what i have above but just reversed?

 

[mdlueck]

Thanks, but isn't there a way to just specify a single record without all that code?

From their example...

  .Edit
  ![ReportsTo] = 5
  ![Title] = "Temporary"
  ![Notes] = rstEmployees![Notes] & "Temp #" & intI
  .Update[FONT=monospace]
[/FONT]

Will bring one record into Edit more, change three fields, and then save the changes by calling Update.

 

[tburn]

From their example...

  .Edit
  ![ReportsTo] = 5
  ![Title] = "Temporary"
  ![Notes] = rstEmployees![Notes] & "Temp #" & intI
  .Update[FONT=monospace]
[/FONT]

Will bring one record into Edit more, change three fields, and then save the changes by calling Update.

Thanks, But I dont want to edit it,. I just want to put it into the variable.
Thanks again, And sorry if i'm being a bit dense! I'm only a beginner!
Tommy

 

[tburn]

Ive just ralised what you were saying - I need to use .edit to read it!

It works now!
Thaks loads!!!
Tommy

 

[mdlueck]

Tommy, I am glad I was able to be of assistance. Thank you for posting that you arrived at a successful result.

 

[harmankardon]

Just as quick note on this, I often use DLookup for situations in which I need to get one value from a table and return it to a variable in VBA and it works great (Just watch out for nulls and a few other gotchas).

I've heard that its a bit slower then using other DAO or SQL based methods but it's so simple to use.

DLookup("FieldName" , "TableName" , "Criteria= 'string'") is the syntax.

Example:

Dim strVariable as String
strVariable = DLookup("DefaultSaveDirectory", "ConfigTable")

Check out Allen Browne's ELookup (Extended DLookup) for an improved version of DLookup.

 

[harmankardon]

Link to Extended DLookup (from my previous post, was still under the 10 post limit for posting links): http://allenbrowne.com/ser-42.html

 

[vbaInet]

(Just watch out for nulls and a few other gotchas).

It might be worth mentioning how you do that?

 

[harmankardon]

It might be worth mentioning how you do that?

The link I posted to Allen Browne's ELookup outlines the shortcomings of DLookup. In my experience, the only issue I ever had was the DLookup function returning a null and that can be handled by either using the isnull() function or the Nz() function.

 

[vbaInet]

Thanks for explaining hamanhardon.