Function to return string

[Acke]

The code has fixed path information on a lot of places in different SQLs (DoCmd.RunSqL command). I want to replace fixed path info with variable path info. Variable path info is stored in the table.

I managed to achieve that in the following manner:

Dim db As Database
Dim dbName as String

   Set db = CurrentDb
   Set rs = db.OpenRecordset ("TableName", dbOpendynaset)
   rs.FindFirst ("ID = " & 2)

   [B]dbName [/B]= rs!TableFieldName
   rs.Close
   db.Close

I replaced path information (\\compName\FolderName\dbname.mdb) with dbName which is the string from table, and it works well.

What I want to do now is to create Module that will save me from writing full code

Dim db As Database
Dim dbName as String

Set db = CurrentDb
Set rs = db.OpenRecordset ("TableName", dbOpendynaset)
rs.FindFirst ("ID = " & 2)

dbName = rs!TableFieldName
rs.Close
db.Close

every time. Something like:

Function VariablePath (dbName as String)

 Set db = CurrentDb
   Set rs = db.OpenRecordset ("TableName", dbOpendynaset)
   rs.FindFirst ("ID = " & 2)

   dbName = rs!TableFieldName
   rs.Close
   db.Close

End Function

where I would use as variable Function name instead of dbName.

As my idea from the above is not working, could you please help me make module that will enable me to use Function name as variable path information for SQL queries?

Thanks!!!

 

[BlueIshDan]

You're assigning a value to the paramater you're sending it but not actually using this parameter at all? I'm not sure what it is that you're trying to accomplish.

 

[Acke]

I would like to make function that will enable me to replace existing paths (\\compName\FolderName\dbName.mdb) with variable as function name.

I already managed to use variable which uses path info from the table. The problem with this solution is that I have to write code in every procedure. That is what I want to safe myself from. I want to make "public" function that will be applicable in all procedures where I need it.

The example of what I need is, instead of:

Dcmd.RunSQL "INSERT INTO [TableName] (Field1, Field2, Field3) IN [B]'\\compName\FolderName\dbName.mdb'[/B]"

I would like to have:

Dcmd.RunSQL "INSERT INTO [TableName] (Field1, Field2, Field3) IN " & [B]FunctionName[/B]

I need help on how to make appropriate function.

Hope it is clearer now.

 

[BlueIshDan]

Are you trying to edit the value of a field within a table?

Ex: Replace all

.[field_name] values that currently = x To y

 

[Acke]

Are you trying to edit the value of a field within a table?

Ex: Replace all

.[field_name] values that currently = x To y

No, I just need to change existing (fixed) paths in various well working queries with variable paths. I need that to avoid changing paths in code in case when Access files are being moved from one computer to another, where computer has different name. I want to change the path in a table and use path information from the table on every place where I have fixed path (\\compName\FolderName\dbname.mdb) at the moment.

 

[BlueIshDan]

I'm sorry but this is all just gibberish for me to read.

 

[pbaldy]

Based on what you have, change this line

dbName = rs!TableFieldName

To

VariablePath = rs!TableFieldName

And try it.

 

[Frothingslosh]

Well, first off, rather than creating a function to look up a computer's path (and your name isn't really well chosen - the variable name leads one to expect the name of the database, not an installation path), I would use a domain function (either DLookup or DFirst, judging by your code), and use that to assign the determined value your variable.

Edit: I'd expect this would work once suitably adapted:

Dcmd.RunSQL "INSERT INTO [TableName] (Field1, Field2, Field3) IN '" & DLookup("FieldName","TableName","ID = " & IDNumber & "';"

 

[pbaldy]

Oh, and change the first line

Function VariablePath () as String

 

[Acke]

Thanks pbaldy, that was the solution.

 

[pbaldy]

Happy to help. Hopefully you're also declaring the variables? Also, I wouldn't use FindFirst, I'd use an SQL statement with that criteria to open the recordset. It would be more efficient, though you may not see a difference on a smaller table.

Frothingslosh's method should also work; I was trying to teach you how to use a function correctly.