Problems with DoCmd.FindRecord

[Grendall]

Morning all,

I am a new user to the forums and to be honest my knowledge of Access still needs a lot of work.

I am trying to create a Database with some simple built in functions but I am running in to a few problems that I will try my best to explain.

My database will be used to record Sales Leads that are passed through from a team of off-site employees. I have posted an image of the tables that I have created and the relationships that I have set.

[image]http://img3.pictiger.com/c7f/14305096_th.jpg[/image]

I have made a simple form for my staff to use when recording these leads.

[image]http://img3.pictiger.com/51d/14305098_th.jpg[/image]

When a CIN is entered, I would like the user to be able to click on the "Check CIN" box to see if the CIN number has already been recorded in a previous record and if so, populate the form with the results. If the CIN number has not been recorded before then just leave the form blank.

I use the code below:

Private Sub Check_CIN_Click()

Screen.PreviousControl.SetFocus
DoCmd.FindRecord Forms!Leads!CIN, acEntire, False, acSearchAll, False, acAll, True

End Sub

I keep getting this error message

[image]http://img3.pictiger.com/632/14305099_th.jpg[/image]

This may be a problem with my relationships or the code it's self but I am at a loss.

Any help that you can give me on this subject would be fantastic.

Graham

 

[Call_Me_Sam]

Graham,

I'm just trying to recreate your problem, but am a bit unsure why you have designed the tables as you have, eg. managers name with managers number in team leader table, when you are joining to the managers table where the name resides.?

Also, the operator number in the Operators table appears to be joined on the operators name in the leads table.

I've not used this method of retrieving results before so maybe barking up wrong tree...

 

[Grendall]

Cheers for the reply Sam,

After taking a look at my database I have made a few changes (you can tell I am new at this lol).



I have now reworked the Relationships to better suit each other and now the connection between the Leads and Operator's Tables should be correct.



I would like users to enter a "CIN" number in the CIN field of my form and then have them click the "Check CIN" button to see if the CIN number has been recorded in any other record. If the CIN has been used before I would like the form to display that record so it may be amended. If the CIN has not been entered before I wish Access to do nothing so the user can continue to fill the form out in the usual way.

Now I have been researching the DoCmd.FindRecord command and I have also been pointed to the follow idea which would work on an After Update basis:

http://www.databasedev.co.uk/duplicates.html

I have no problems in posting my database or sending it to someone but I hate to admit I completely lack to knowledge to get this working and I am beginning to lose sleep over this now lol

Thanks again for all your help,

Graham

 

[gemma-the-husky]

you may be better with a dlookup

check the underlying table to see whether the required item exists

ONLY if it exists, do you need to goto the record


so its like

{the syntax needs a bit of care here}
recordid = nz(dlookup("recordid","targettable","CIN = " & targetcin),0))
if recordid<>0 then
{now navigate to the correct record}
end if

 

[Call_Me_Sam]

Graham/Gemma,

would it not be more straight forward to have a search field in a header section, which if the record did not exist then simply displayed a new record, or else a combo box linked to the CIN so one could be selected.

I haven't used dlookup that much so would perform this action otherwise. Like everything, i am curious of it's use.

 

[Grendall]

Thanks for the replies again,

Gem, I have had a look at the code you have posted and I am afraid that my knowledge of VBA is so rubbish it makes little sense to me - I know this is not the kind of thing anyone wants to hear

Sam, your idea sounds good, but again adding a search field is again out of my technical range (I know I am crap).

I have been looking at the following:

Private Sub strStudentNumber_BeforeUpdate(Cancel As Integer)

'*********************************
'Code sample courtesy of srfreeman
'*********************************

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strStudentNumber.Value
stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

'Check StudentDetails table for duplicate StudentNumber
If DCount("strStudentNumber", "tblStudentDetails", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Student Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", vbInformation _
, "Duplicate Information"
'Go to record of original Student Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub

Now obviously I don’t have a "strStudentNumber" or a "tblStudentDetails" but I have tried to replace these items with fields and tables I do have but I still don’t get past the first line of code, that’s how little my knowledge is I am afraid.

The idea of the above code comes from:

http://www.databasedev.co.uk/duplicates.html

Graham

 

[Call_Me_Sam]

you'll get me shot Graham if my boss sees me doing this but...

'Declaring variables
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

'loads a temporary recordset with a clone of the bound tables data
Set rsc = Me.RecordsetClone

'stores the studentID
SID = Me.strStudentNumber.Value
'sets up a 'search' variable using the studentID
stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"

'Well uses DCOunt to check for the search variable in the field studentnumber in the table studentdetails
'Check StudentDetails table for duplicate StudentNumber
If DCount("strStudentNumber", "tblStudentDetails", stLinkCriteria) > 0 Then

'Undo duplicate entry - self explains IT WILL UNDO YOUR CHANGES
Me.Undo

'Message box warning of duplication - displays message
MsgBox "Warning Student Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", vbInformation _
, "Duplicate Information"

'Go to record of original Student Number - searches for record
rsc.FindFirst stLinkCriteria
'navigates to the record using bookmark..like it might use that in future..
Me.Bookmark = rsc.Bookmark
End If

'close recordset
Set rsc = Nothing
End Sub

Hopefully what i've added makes sense...i did raise the piont that you could corrupt the table underneath if you allow the user to overwrite the primary key to a number that is still unique..but it was useful code.

 

[Grendall]

Thanks for all your help Sam, I don't want you to get in trouble at work mate.

I have tried to manipulate the code the best I can and I get the below come up:

http://img79809.pictiger.com/images/14327703/

Now you may look at this and think "god Graham you are a FOOL" and if this is the case please say so he he.

Also Sam, you have spent so much time and effort helping me out, if you give me the name or website of a charity you support I will make a donation on your behalf as a thank you for all your help.

Graham

PS How the hell do you know so much about Access

 

[Call_Me_Sam]

graham,

it sounds like you are a generous person, both with your words and appreciation...but compared to others on this forum i am but a learner like you. they say charity begins at home, invest in yourself and use that investment to help others.

My boss is flexible i meant no guilt trip...he struggles with sumif functions in Excel, but knows which way up a pivot table should be held so the numbers don't drop out.

You made the wisest choice posting your question on a forum, many simple sit back and weep that it doesn't work....

 

[Grendall]

Cheers Sam,

I finally used a different method to use an un-bound text box as the search field to display any matching records:

Private Sub cmdSearch_Click()
Dim strCustomerRef As String
Dim strSearch As String

If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![txtSearch].SetFocus

Exit Sub
End If

DoCmd.ShowAllRecords
DoCmd.GoToControl ("CIN")
DoCmd.FindRecord Me!txtSearch
CIN.SetFocus
strCustomerRef = CIN.Text
txtSearch.SetFocus
strSearch = txtSearch.Text

If strCustomerRef = strSearch Then
CIN.SetFocus
txtSearch = txtSearch

Else

DoCmd.GoToRecord , , acNewRec
txtSearch.SetFocus

End If
End Sub

Seems to work great however I need 1 last helping hand to get the polished result I am after.

When the code has run its course I want the value of the Unbound Text box to be copied into the CIN Control on my form, but again I have no idea of the VBA code to effectively "Copy & Paste" the value.

Any ideas?

Graham

 

[Call_Me_Sam]

it should be as simple as stating

CIN.text = txtSearch.text

You can use .text or .value, not entirely sure of the difference, if any. If you omit either then the system takes a default .value anyway...

you effectively do the same here

strCustomerRef = CIN.Text

strSearch = txtSearch.Text