Search results

Thank you so much for all your help! :D

That worked like a charm. In the interest of learning, would you mind (at your leisure of course) commenting the code so that I can understand a little better why it worked?

So I removed the date field from the equation and replaced it with the vendor ID. It works as anticipated when the record is a duplicate. However, when I enter a new record I get a "Run time error 3021, No Current Record." It says the issue is at the part highlighted in red. Private Sub...

I had a feeling at the beginning that I would need to loop through a recordset at some point, I just didn't know when, where, or how. If you haven't already suspected, I'm not altogether proficient with using sql in vba. When I copied the code over to access, there appears to be an issue with...

I replaced the DCount with the query statement above and I also set my if statement to check if the Invoice Variable was greater than 0. I still can't enter any records, new or duplicate. Here is my code. Private Sub Form_BeforeUpdate(Cancel As Integer) Dim Invoice As Variant Invoice =...

Could you provide steps/coding to get this done?

How do I set up the query?

Next Attempt: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim Invoice As Variant Invoice = DCount("*", "tblMaintenance", _ "[Amount]=" & Me.Amount & "And [InvoiceDate]=" & Me.InvoiceDate & _ "And [InvoiceNum]='" & Me.InvoiceNum & "'") If Not...

The VendorID is numeric. I have a combo box on the form that inputs the VendorID for a specific Vendor into the VendorID field on the form.

So here's some code I actually got to work. Private Sub Form_BeforeUpdate(Cancel As Integer) Dim Vendor As Variant Dim Amount As String Dim InvoiceDate As Variant Dim InvoiceNum As String Dim Answer As String 'Vendor = DLookup("[VendorID]", "tblMaintenance", "[VendorID] = " &...

Which would be quicker to run? The table right now stands at about 90,000 records. Would the query have to run through every record to determine if it was a duplicate? Would the VBA code run faster?

So I'm attempting to adopt some code that I came across in an effort to prevent people from double entering invoices. I think I understand the basic premise which is to use the DLookup to compare what has been entered to values already existing in the table and if it matches, then prevent the...

namliam, Thank you for all your help. I now have a function that does exactly what I want it to do. I was able to make the changes on the code myself and this is what I came up with. Private Sub WorkOrdercmdBut_Click() If MsgBox("This will open the Excel folder for spreadsheet...

msk7777, I found that code on another website, unfortunately I've misplaced the address. When I found the code though, the original designer set the end range as "A65565". Essentially (at least as I interpret it) the code counts from end of your "static" range up to the last row you have text...

Ok, so I've amended the method I'm using to this code I found here http://www.access-programmers.co.uk/forums/showthread.php?t=158308. Below is how I've amended it: Private Sub WorkOrdercmdBut_Click() If MsgBox("This will open the Excel folder for spreadsheet imports. Continue?"...

This definitely makes sense and I will try to implement the change when I go to work this morning. It would be much easier for everything to be on one sheet instead of the directions on one and the work sheet on the other. Invariably, people will not look at the directions and assume the know...

So I created a work around for my issue above. Instead of trying to make the named range start at "B27", I just moved the directions to their own sheet and made the named range start at "B1". This seems to have corrected the issue and now everything works as it should. Now for my second issue...

I can confirm that MSK and I are not working on a class assignment and each of us need help in our own right. We're not even in the same state and both are working professionals. Do not judge harshly and please help if you can.

Definitely not a class assignment. Been out of college a number of years at this point. Essentially, I'm going to distribute an excel sheet that will allow various locations to submit a work order. Each item needing repaired will have it's own line item, but there's no telling how many lines...

I need the range to start in B27 and go 9 columns across the page. The "A3" you bolded was a part of a comment from the original code. I've put the new code below and removed the comment. Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim lastRow As Long Dim Range1 As Range lastRow =...