Go To Record If Duplicate

G

GendoPose

Registered User.
Local time
Today, 20:41
Joined
Nov 18, 2013
Messages
175
Hi All,

On my form I've got an afterupdate event that checks if the information entered already exists and this works absolutely fine. However what I would like is the option to go to the existing record if one is found, but I can't get it to work.

This is my code currently;

Code: 
Private Sub Job_No_AfterUpdate()
    If DCount("*", "PACKING", "[Job No>]='" & Me.[Job No] & "'") > 0 Then
        If MsgBox("Job Number already exists! Go to record?", vbYesNo, "DST PLANNER") = vbYes Then
            Dim rs As Object
            Dim lngBookmark As Long
                lngBookmark = Me.[Job No>]
                DoCmd.OpenForm "Packing"
                Set rs = Forms!Packing.RecordsetClone
                rs.FindFirst "[Job No>] =" & lngBookmark
                Forms!Packing.Bookmark = rs.Bookmark
                Set rs = Nothing
        Else
            DoCmd.CancelEvent
        End If
    End If
End Sub

The check for the Job Number works fine but when I click Yes on the message box, the form stays on the current record instead of moving to the existing record.

Any help?

Thanks guys
 
Why not use the Before update and prevent the duplicate from happening?
 
pr2-eugin said:
Why don't you open the Form to that record, using the WHERE condition of the DoCmd.OpenForm method? http://baldyweb.com/wherecondition.htm
Click to expand...

The form is already open when it makes the check, I tried adding DoCmd.Close before the check but obviously then the function stops because the form is closed.

jdraw said:
Why not use the Before update and prevent the duplicate from happening?
Click to expand...

Because I need it to check after I've entered in the Job Number, as very occasionally there may be a need to repeat that Job Number with different information involved.
 
Then why do you have the DoCmd.OpenForm? Makes no logical sense, comment out that line and see what happens. Also this JobNo control is unbound right?
 
pr2-eugin said:
Then why do you have the DoCmd.OpenForm? Makes no logical sense, comment out that line and see what happens. Also this JobNo control is unbound right?
Click to expand...

Didn't change anything although got to admit I didn't even notice that. No it's a bound control on a form.
 
Okay, create a New control on the Form (unbound) best it to be a combo box. Set its Rowsource as the list of all Job no's from the table, something like.
Code: 
SELECT PACKING.[Job No] FROM PACKING;
Then use the combobox's after update to do what you are doing now.
 
pr2-eugin said:
Okay, create a New control on the Form (unbound) best it to be a combo box. Set its Rowsource as the list of all Job no's from the table, something like.
Code: 
SELECT PACKING.[Job No] FROM PACKING;
Then use the combobox's after update to do what you are doing now.
Click to expand...

Nope, still can't get this to work!
 
Please post a Stripped DB.

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)
 
pr2-eugin said:
Please post a Stripped DB.

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)
Click to expand...

Attached stripped down database. The form is called Packing, the combo box you suggested is the very first one in the layout.
 

Attachments

Is there really only 1 table in your database?
Can you give us a brief overview of the purpose of the database?
 
pr2-eugin said:
Check out the file !
Click to expand...

I can't quite see what you've done here?

The search at the top is literally just a quick search, nothing more. The textbox in the form (txtJobNo) is where I originally had my code and the combobox cboJobNo is the one you said to put in.
 
jdraw said:
Is there really only 1 table in your database?
Can you give us a brief overview of the purpose of the database?
Click to expand...

No, this is just a small part of it stripped down.

This section is to provide packing and goodsout instructions to our warehouse and to keep track of all jobs going through.
 
I did not know what you did, I just took your word
GendoPose said:
the very first one in the layout.
Click to expand...
The very first one I saw was Combo58. For which I have coded it to go to the specific record.

This is the code you had,
Code: 
Private Sub cboJobNo_AfterUpdate()
    If DCount("*", "PACKING", "[Job No>]='" & Me.cboJobNo & "'") > 0 Then
        If MsgBox("Job Number already exists! Go to record?", vbYesNo, "DST PLANNER") = vbYes Then
            lngBookmark = Me.cboJobNo
            Set rs = Forms!Packing.RecordsetClone
            rs.FindFirst "[COLOR=Red][B][Job No>] [/B][/COLOR]=" & lngBookmark
            Forms!Packing.Bookmark = rs.Bookmark
            Set rs = Nothing
        Else
            DoCmd.CancelEvent
        End If
    End If
End Sub
What I put in the Combo58 is,
Code: 
Private Sub Combo58_AfterUpdate()
    If MsgBox("Go to record?", vbYesNo, "DST PLANNER") = vbYes Then
        lngBookmark = Me.Combo58
        Set rs = Forms!Packing.RecordsetClone
        rs.FindFirst "[COLOR=Red][B][ID][/B][/COLOR] =" & lngBookmark
        Forms!Packing.Bookmark = rs.Bookmark
        Set rs = Nothing
    End If
End Sub
Your Code looks for JobNumber, my code looks for ID for which the ComboBox is bound to.

So now, what is your requirement?
 
pr2-eugin said:
I did not know what you did, I just took your word
The very first one I saw was Combo58. For which I have coded it to go to the specific record.

This is the code you had,
Code: 
Private Sub cboJobNo_AfterUpdate()
    If DCount("*", "PACKING", "[Job No>]='" & Me.cboJobNo & "'") > 0 Then
        If MsgBox("Job Number already exists! Go to record?", vbYesNo, "DST PLANNER") = vbYes Then
            lngBookmark = Me.cboJobNo
            Set rs = Forms!Packing.RecordsetClone
            rs.FindFirst "[COLOR=red][B][Job No>] [/B][/COLOR]=" & lngBookmark
            Forms!Packing.Bookmark = rs.Bookmark
            Set rs = Nothing
        Else
            DoCmd.CancelEvent
        End If
    End If
End Sub
What I put in the Combo58 is,
Code: 
Private Sub Combo58_AfterUpdate()
    If MsgBox("Go to record?", vbYesNo, "DST PLANNER") = vbYes Then
        lngBookmark = Me.Combo58
        Set rs = Forms!Packing.RecordsetClone
        rs.FindFirst "[COLOR=red][B][ID][/B][/COLOR] =" & lngBookmark
        Forms!Packing.Bookmark = rs.Bookmark
        Set rs = Nothing
    End If
End Sub
Your Code looks for JobNumber, my code looks for ID for which the ComboBox is bound to.

So now, what is your requirement?
Click to expand...

Sorry my mistake, I meant the combo box in the detail, Combo58 is just a macro search that searches by ID.

On the after update event of txtJobNo, I want to check for duplicate records and then if there is one, I want to find that record.

Thanks for the help so far!
 
You do realize that the Bound control, when you change will actually change your underlying data?

I am still not sure what is that you actually want to do ! You want to search by job no, which is what Combo58 does for you, then again you say that it is a macro to search for an ID. :eek: I am totally lost ! Sorry !
 
pr2-eugin said:
You do realize that the Bound control, when you change will actually change your underlying data?

I am still not sure what is that you actually want to do ! You want to search by job no, which is what Combo58 does for you, then again you say that it is a macro to search for an ID. :eek: I am totally lost ! Sorry !
Click to expand...

Ah that's what the save function I built in is for, nothing is finallised until that save button is clicked.

All I want is txtJobNo to search for a duplicate and then go that duplicate record if there is one. Combo58 is just a quick search function, allowing us to find a job and tick it as completed or make any changes to the record. The purpose of checking for duplicates in this way is so that when we have several jobs to put on the database (could be more than 20 at a time) we don't have to spend time searching for the job using Combo58.

Sorry for any confusion!
 
You must log in or register to reply here.

Similar threads

A
Autocomplete Combo Enter/Tab behaviour
Replies
2
Views
331
xavier.batlle
X
A
“Add A New Record” function is not working
Replies
12
Views
1,172
access2010
A
C
Solved Treeview address
Replies
8
Views
825
ClaraBarton
C
A
Search box to find parts to add to job instead of a combo box
2 3
Replies
40
Views
2,373
Gasman
Gasman
D
Opening record by ID opens a new record
Replies
8
Views
635
Mark_
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom