Hello-
I am trying to perform an update to an existing record but am getting a runtime error. I believe that it cannot find my field in my datasheet however the record is there. I can search is with another command button and it retrieves my records, however, when I try to update it doesn't work. I know that this is something simple but being new to VB I cannot seem to debug it. Any help is appreciated.
Thanks.
Please Login or Register to view this content.
Last edited by Leith Ross; 10-26-2015 at 12:41 PM. Reason: Addded Code Tags
Hello hdrickard,
From your code, it appears you are trying to access a text box on the same worksheet. The SetFocus method will work if the text box is on a UserForm, but not on a worksheet. This will generate an error. To avoid this error you must use a different method. You should post a copy of your workbook for a specific answer.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Thanks Leith.
Hello hdrickard,
Here is the working macro. When using the Find method, you can avoid errors simply by testing if the returned Range object is Nothing.
I added code to remove the close "X" on the UserForm. Annoying your users by telling them to click another button is a bad practice. I also added icons and shortcut keys to the search and close buttons.
Please Login or Register to view this content.
Thank you for taking the time to look into this and provide tips on improving the function. The search function appears to work fine, however, it is the Check In routine that is failing. it does not seem to be able to locate the record already on file to perform the update??
Private Sub cmdCheckIn_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Detail")
'error block
On Error GoTo errHandler:
'check for a shipment id number
If Trim(Me.txtShipmentIDci.Value) = "" Then
Me.txtShipmentIDci.SetFocus
MsgBox "Please enter the Shipment ID to be checked in."
Exit Sub
End If
If WorksheetFunction.CountIf(ws.Range("A", ws.Cells(iRow, 1)), Me.txtShipmentIDci.Value) > 0 Then
ws.Cells(iRow, 3).Value = "CHECK IN"
Else
MsgBox "Shipment ID not checked out!", vbCritical
Exit Sub
End If
'clear the data
Me.txtShipmentIDci.Value = ""
Me.txtShipmentIDci.SetFocus
'error block
On Error GoTo 0
Exit Sub
errHandler:
MsgBox "Shipment ID Number not Found"
End Sub
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
If posting code please use code tags, see here.
# Accidental dup #
Thank you for taking the time to look into this and provide tips on improving the function. The search function appears to work fine, however, it is the Check In routine that is failing. it does not seem to be able to locate the record already on file to perform the update??
Please Login or Register to view this content.
Last edited by hdrickard; 10-30-2015 at 12:28 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks