VBA for Update Query (1 Viewer)

RiskyP said:

Hi,

I am creating a database that requires a user to link a file to a record in the database. To do this I am currently using an update query, The user manually 'copy as link' from the file and pasting into the update to field. The issue is - Copy As Link adds "'s before and after the link text, which is not read by access as a link and therefore renders my link unclickable. To get around this i have created a form to paste the As Link into, with a VBA that replaces both "'s with #'s. If your following me so far.

My VBA perameters set this new #Link# string as 'Lnkedit'

The VBA then initiates the update query.

My issue is - How do I tell the VBA to populate the 'Update to' parameter with my Lnkedit string? While still allowing the user to select my Query criteria Parameters that specify which records the file is linked to?

Or am i overcomplicating things - is there an easier way to link 1 object to several different records on my database?

Thanks,

Click to expand...

jzwp22 said:

Since the value in the control is a variable, it cannot be enclosed within the double quotes but must be concatenated to the string. Additionally, if the vessel name field is a text data type then the value from the control must be delimited with single quotes.

"WHERE Tbl.Fields(Vessel Name) ='" & Me.VslNme.Value & "'"

The Lnkedit value would also have to be delimited with single quotes

One last thing, I'm not sure how VBA will respond to the field and table names since they include spaces. You may have to enclose them in square brackets.

If the form is in the database you mention, I'm not sure why you need the tableDef syntax. The code could be simplified as follows:

Code:Copy to clipboard

Dim lnk As String
Dim Lnkedit As String
 
 
lnk = Me.LinkBox.Value
Lnkedit = Replace(lnk, """", "#")
 
dim mySQL as string
 
 
mySQL= "UPDATE [Master Table - Packages] SET [Package Recepit] = '" & Lnkedit  & "' WHERE [Vessel Name] ='" & Me.VslNme & "'"
 
currentDB.execute mySQL, dbfailonerror

Click to expand...

jzwp22 said:

If the additional criteria is what you need, there would be no problem adding it.

If the vessel date field is a date/time data type, it must be delimited by # signs. This is what the statment would look like. You will have to add the control/variable that provides the date value

mySQL= "UPDATE [Master Table - Packages] SET [Package Recepit] = '" & Lnkedit & "' WHERE [Vessel Name] ='" & Me.VslNme & "' AND [Vessel Date]=#" & SomeControlOrVariableReference & "#"

BTW, I typically put the SQL text in a variable because then you can use the debug.print statment to copy the constructed text to the VBA Immediate window for error checking. It is not a necessary statement, but if you are having issues with a query, it makes it easier to troubleshoot if you can see the actual text that was created. The other good thing about it is that you can copy the text from the Immediate window to a new query & try to run it from there and see what errors are returned.

The command would look like this:

debug.print mySQL

The command can be placed anywhere in your code after the mySQL variable is populated/constructed. Once you have done all of your troubleshooting of the code, you would either comment out the debug command or just delete it.

Click to expand...

jzwp22 said:

If the additional criteria is what you need, there would be no problem adding it.

If the vessel date field is a date/time data type, it must be delimited by # signs. This is what the statment would look like. You will have to add the control/variable that provides the date value

mySQL= "UPDATE [Master Table - Packages] SET [Package Recepit] = '" & Lnkedit & "' WHERE [Vessel Name] ='" & Me.VslNme & "' AND [Vessel Date]=#" & SomeControlOrVariableReference & "#"

BTW, I typically put the SQL text in a variable because then you can use the debug.print statment to copy the constructed text to the VBA Immediate window for error checking. It is not a necessary statement, but if you are having issues with a query, it makes it easier to troubleshoot if you can see the actual text that was created. The other good thing about it is that you can copy the text from the Immediate window to a new query & try to run it from there and see what errors are returned.

The command would look like this:

debug.print mySQL

The command can be placed anywhere in your code after the mySQL variable is populated/constructed. Once you have done all of your troubleshooting of the code, you would either comment out the debug command or just delete it.

Click to expand...