Is this popup box possible?

[TigerGyrlAlly]

I'm designing a form and the person that it is for has requested the following:

To be able to scan a code that will populate 4 fields, the code field, the name field, a number field, and a date field that is automatically populated with the current date at the time the scan is done.

To be able to scan the same code more than once and be prompted with a popup box that states the current date field and gives you the option of overwriting the existing entry with the new date. Pressing yes will overwrite the data and no will cancel the overwrite, close the popup and bring you to a new record where new data can be entered.

I am lost as to how or even if this task can be completed.

 

[NateO]

I think so. A few questions. Which version of Access are you using? What are you overwriting, just the date? Are there duplicate dates?

 

[TigerGyrlAlly]

Access 2000

You would only have to overwrite the date.

What happens is there are 4 fields: ID, name, number, date

When the barcode is scanned, the ID, name and number fields are populated and the date field is filled in automatically with the current date.

Usually a query is run to update the main table with this newly scanned information and also to clear out the data in the temporary holding table.

However, there may be times where the query is not run at the end of the day and the barcode will get scanned again on the next day. In this case, the user wants to be prompted with a pop up box that lists the date currently in the date field and she wants to be given the choice of overriding the current value with the current date.

The Id, name and number will always be the same regardless of when the barcode is scanned...

 

[NateO]

So you're comparing say the name fields on two different tables at some given random point in time and looking for differing dates?

 

[TigerGyrlAlly]

What would happen is that you have FormA.

A barcode is scanned and the Id, name, number and date is populated on form A and then sent to Table 1

There is a query that will run and update Table 2 with the information in Table 1 and then empty Table 1.

What I want to do, is in the event that the query is not run, and a barcode is scanned again that would populate the Id, name, number and date fields, is to find EACH record where the id field that i'm currently scanning, already exists. If a record is found for this id I want a popup box to be displayed showing the current value in the date field and giving me the option of overriding the old date with the current date. If I choose yes I want the current date to be inserted into the date field and if I choose no then I want the old date to stay in the date field.

 

[NateO]

Right, here's one possibility with ADO, it uses the name field on Table 1, but perhaps you can tweak the sql more to your liking:

<font face=Courier New><SPAN style="color:darkblue">Private</SPAN> <SPAN style="color:darkblue">Sub</SPAN> CommandButton1_Click()
<SPAN style="color:darkblue">Dim</SPAN> rs <SPAN style="color:darkblue">As</SPAN> ADODB.Recordset, mySql <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>
mySql = "SELECT [date], [name] " & _
    "FROM Table1 WHERE " & _
    "[name]=<SPAN style="color:green">'" & Text1.Value & "';"</SPAN>
<SPAN style="color:darkblue">Set</SPAN> rs = <SPAN style="color:darkblue">New</SPAN> ADODB.Recordset
rs.<SPAN style="color:darkblue">Open</SPAN> mySql, CurrentProject.Connection, adOpenStatic, adLockOptimistic
mySql = <SPAN style="color:darkblue">Empty</SPAN>
<SPAN style="color:darkblue">With</SPAN> rs
    <SPAN style="color:darkblue">If</SPAN> .RecordCount > 0 <SPAN style="color:darkblue">Then</SPAN>
        .MoveFirst
        <SPAN style="color:darkblue">Do</SPAN> <SPAN style="color:darkblue">Until</SPAN> .EOF
            <SPAN style="color:darkblue">If</SPAN> <SPAN style="color:darkblue">Not</SPAN> .Fields("date") = <SPAN style="color:darkblue">Date</SPAN> <SPAN style="color:darkblue">Then</SPAN>
                <SPAN style="color:darkblue">Select</SPAN> <SPAN style="color:darkblue">Case</SPAN> MsgBox("Old <SPAN style="color:darkblue">Date</SPAN> Found: " & .Fields("date") & vbLf & vbLf & _
                    "Update Date?", vbYesNo)
                    <SPAN style="color:darkblue">Case</SPAN> vbYes:
                        .Fields("date") = <SPAN style="color:darkblue">Date</SPAN>
                        .Update
                    <SPAN style="color:darkblue">Case</SPAN> vbNo
                <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Select</SPAN>
            <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
            .MoveNext
        <SPAN style="color:darkblue">Loop</SPAN>
    <SPAN style="color:darkblue">Else</SPAN>
        .AddNew
        .Fields("name") = Text1.Value
        .Fields("date") = <SPAN style="color:darkblue">Date</SPAN>
        .Update
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
    .<SPAN style="color:darkblue">Close</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
<SPAN style="color:darkblue">Set</SPAN> rs = <SPAN style="color:darkblue">Nothing</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

Add your other two fields and controls as well. And, your Case vbNo scenario if you want to. Hope this helps.

 

[TigerGyrlAlly]

I have another question, I tweaked the code a bit to make it look for vendorid because this is the field where the code should find if a number is entered more than once, then compare the dates.

My question now is, where/how do I place this into the code? I have the form setup and a command button that updates the main table. My original thought plan was to link this popup box so that when the update query is running the popup box would appear as each duplicate entry was found. However, looking at the code it seems that this box works in a way, that the message box appears before the query runs. Am I understanding this correctly?

Also I get an error, "user defined type not defined"

I have never used vba code until this so I am really lost

 

[NateO]

Nope, it runs after the query is performed, working on the streamlined data. You can see the query being run here:

rs.Open mySql, CurrentProject.Connection, adOpenStatic, adLockOptimistic

Very early in the procedure.

Right-click on your form's button in design mode, and left-click build event. Should take you to the correct click event. You want the code burried in the correct form module, which this process facilitates.

What's highlighted with your error? One of the fields? Text1.value?

 

[TigerGyrlAlly]

Okay, I linked it to the button event Dblclick.

However, when it runs there is a compile error, this is highlighted:

rs As ADODB.Recordset

and the error message is "user type not defined."

You said that it runs after the query has run, but that can't happen if the query is an update query - it will have already updated the fields. Is there a way to tweak this code so that when the "Update" button is pressed, the code will run through the data to find any duplicate vendorids and give the popup message to allow the user to overwrite the date if they would like to OR have the code work so that as the update query is running, this code is running also and if a duplicate record exits, before the record is updated, the message box pops up to ask you to overwrite the date or not?

As I said before, I have never used vba code before so i'm not sure of any of this - I mean, is any of this possible at all? Thanx for the help you've given me!!

If it helps, i've posted the code as it appears in my project:

Private Sub Both_Buttons_DblClick(Cancel As Integer)

Dim rs As ADODB.Recordset, mySql As String
mySql = "SELECT vendorid, name, receiveddate " & _
"FROM Barcodescaninputs WHERE " & _
"vendorid='" & VendorID & "';"
Set rs = New ADODB.Recordset
rs.Open mySql, CurrentProject.Connection, adOpenStatic, adLockOptimistic
mySql = Empty
With rs
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
If Not .Fields("receiveddate") = Date Then
Select Case MsgBox("Old Date Found: " & .Fields("receiveddate") & vbLf & vbLf & _
"Update Date?", vbYesNo)
Case vbYes:
.Fields("receiveddate") = Date
.Update
Case vbNo
End Select
End If
.MoveNext
Loop
Else
.AddNew
.Fields("vendorid") = VendorID
.Fields("receiveddate") = Date
.Update
End If
.Close
End With
Set rs = Nothing


DoCmd.OpenQuery "updatetblvendors", acViewNormal
DoCmd.OpenQuery "clearbarcodes", acViewNormal
MsgBox "Update complete.", vbOKOnly, "Attention"
End Sub

 

[NateO]

This is it's own update query. If you're talking about another one, there's no need for it, this queries the data for duplicates, if it finds one, or many actually, the way it was written, it will update those records. If not, it will add records. You can see it passing an sql statement, this is select sql statement.

You're using Access 2000? Shouldn't be a library reference order issue, the way it's explicitely written. Do you have a reference set in your VBE to Microsoft ActiveX Data Objects x? Check it out, tools->References.

Sorry, not following the bit re: Update Query.

Get rid of:

DoCmd.OpenQuery "updatetblvendors", acViewNormal

This is already happening with the VBA recordset query, right here:

.Fields("receiveddate") = Date
.Update

This is the best way to do this per your request I believe. Otherwise you're doing stuff with temporary tables, etc... That is if I follow what your asking. I don't see the point of giving someone the prompt then turning around and updating the data anyway. If this sounds pointy I apologize, just thinking critically aloud.