RUSS_EMI
asked on
How do I lookup a record from within a form, if the primary key is unknown. Using VBA.
Probably a simple question, but here goes;
I have a unique field value, which isn't and can't be the primary key to a record (a fingerprint scan).
I need to lookup the correct record that matches, if any exist.
I know how to do this in VB6 with -
sSQL1 = "SELECT * FROM Employees WHERE Employees.fkAlpha = " + "'" + Me.FkAlpha + "'"
Set DB = OpenDatabase("c:\employees .mdb")
Set rst = DB.OpenRecordset(sSQL1)
rst.MoveLast
rst.MoveFirst
What do I do if I'm in a form in Access?
I need to have this done automatically, so I can display the record for the user.
Thanks,
Russ
I have a unique field value, which isn't and can't be the primary key to a record (a fingerprint scan).
I need to lookup the correct record that matches, if any exist.
I know how to do this in VB6 with -
sSQL1 = "SELECT * FROM Employees WHERE Employees.fkAlpha = " + "'" + Me.FkAlpha + "'"
Set DB = OpenDatabase("c:\employees
Set rst = DB.OpenRecordset(sSQL1)
rst.MoveLast
rst.MoveFirst
What do I do if I'm in a form in Access?
I need to have this done automatically, so I can display the record for the user.
Thanks,
Russ
rst.FindFirst "[Employees.fkAlpha]='" & Me.FkAlpha & "'"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, I'll try it tomorrow and get back to you.
Russ
Russ
as another suggestion, if your in a form, create the recordset as above then use recordsetclone method (full details in the help file - or give us a shout)
:-)
:-)
While all of this will work, the question is do you need all the fields from the record or just, say, the IDField. In the case of the latter, i agree with nestorio
somevalue = dlookup("IDField", "Employees", "[fkAlpha] = '" + Me.FkAlpha + "'")
Walt
somevalue = dlookup("IDField", "Employees", "[fkAlpha] = '" + Me.FkAlpha + "'")
Walt
ASKER
Dlookup works great. I do need to initialize the form & display the complete record. I would like to leave the record blank if no match is found. Could someone give me the code for that, please?
Thanks,
Russ
Thanks,
Russ
Hi Russ
Looking at what you want now, then you don't want a Dlookup, what you want is a filter, to filter a From.
Suppose your Form which will show all the data is called FormA,
Have another Blank Form, which will act as a switchboard OR I use a simple Main Page, lets call it FormB,
FormB will have two command buttons and a ComboBox OR ListBox,
You select your Record from the ComboBox then click on the command button (Captioned Open Record) to open FormA, which will contain all the Records,
If the Record you want to select is Not in the ComboBox, then that means its not in the Table, so Click on the second command button (Captioned New Record) should open a New Record for you.
Does this sound OK?
Code for (Captioned Open Record)
DoCmd.OpenForm "FormA", acNormal, , "[fkAlpha]=Forms![FormB]![ ComboBoxNa me]", acFormEdit, acWindowNormal
Code for (Captioned New Record)
DoCmd.OpenForm "FormA", acNormal, , , acFormAdd, acWindowNormal
jaffer
Looking at what you want now, then you don't want a Dlookup, what you want is a filter, to filter a From.
Suppose your Form which will show all the data is called FormA,
Have another Blank Form, which will act as a switchboard OR I use a simple Main Page, lets call it FormB,
FormB will have two command buttons and a ComboBox OR ListBox,
You select your Record from the ComboBox then click on the command button (Captioned Open Record) to open FormA, which will contain all the Records,
If the Record you want to select is Not in the ComboBox, then that means its not in the Table, so Click on the second command button (Captioned New Record) should open a New Record for you.
Does this sound OK?
Code for (Captioned Open Record)
DoCmd.OpenForm "FormA", acNormal, , "[fkAlpha]=Forms![FormB]![
Code for (Captioned New Record)
DoCmd.OpenForm "FormA", acNormal, , , acFormAdd, acWindowNormal
jaffer
ASKER
I'm not sure that will work. Here is the exact situation;
I've got a fingerprint scanner that has a control which returns BioCert1.fkAlpha.
This value is a unique value (but not the key) in each record.
I am doing a Validate form, so if a fingerprint is found, the complete record is displayed and a timestamp is placed on the record.
If no match is found, a blank record with a red background is displayed.
I don't want a combo box, or list - the record must be selected automatically on return of the BioCert1.fkAlpha.
Hope this clears up what I'm looking for.
Russ
I've got a fingerprint scanner that has a control which returns BioCert1.fkAlpha.
This value is a unique value (but not the key) in each record.
I am doing a Validate form, so if a fingerprint is found, the complete record is displayed and a timestamp is placed on the record.
If no match is found, a blank record with a red background is displayed.
I don't want a combo box, or list - the record must be selected automatically on return of the BioCert1.fkAlpha.
Hope this clears up what I'm looking for.
Russ
Yes it does, and the solution I gave will work, but instead of command buttons and a combobox, everything will be done in code,
But a question first,
When a person scans his finger print, which returns BioCert1.fkAlpha
is BioCert1.fkAlpha a normal field in Table/Form?
jaffer
But a question first,
When a person scans his finger print, which returns BioCert1.fkAlpha
is BioCert1.fkAlpha a normal field in Table/Form?
jaffer
let me ask my question in a different manner:
when a person scans his finger print,
does the BioCert1.fkAlpha show in a Field of an open Form?
when a person scans his finger print,
does the BioCert1.fkAlpha show in a Field of an open Form?
ASKER
not BioCert1.fkAlpha, but Me.fkAlpha (from the Employee table) is normally displayed. Along with all other fields in that table / record.
Russ
Russ
Good, so here is what we can do,
Copy FormA to FormA2, then change the Colors of FormA2 to Red Or anything that you like,
This way you have FormA (When Data Exists),
FormA2 (When New Data will be entered), set the "Data Entry" of the Form to YES (in the Form properties),
FormB (Another Form which, the scan the finger print value will show in it)
In FormB you will have an UnBound Field [fkAlpha], and a command button.
Once the the scan is done, [fkAlpha] field will be filled in,
then you have to click on the command button (although it can be done without it, but it will limit you),
have the following code On Click of the command button:
DoesItExist=dlookup("[IDFi eld]", "Employees", "[fkAlpha] = '" + Me.FkAlpha + "'")
if isnull(DoesItExist) then
'Open a New Record.
DoCmd.OpenForm "FormA2", acNormal, , , acFormAdd, acWindowNormal
'Copy the Finger print data from FormB to FormA2
Forms![FormA2]![fkAlpha] = Forms![FormB]![fkAlpha]
else
'Open an existing Record based on [fkAlpha]
DoCmd.OpenForm "FormA", acNormal, , "[fkAlpha]='" + Me.FkAlpha + "'"
endif
-------------------------- ----
Please note that this solution is a combination of walterecook's and Nestorio's as well as mine.
jaffer
Copy FormA to FormA2, then change the Colors of FormA2 to Red Or anything that you like,
This way you have FormA (When Data Exists),
FormA2 (When New Data will be entered), set the "Data Entry" of the Form to YES (in the Form properties),
FormB (Another Form which, the scan the finger print value will show in it)
In FormB you will have an UnBound Field [fkAlpha], and a command button.
Once the the scan is done, [fkAlpha] field will be filled in,
then you have to click on the command button (although it can be done without it, but it will limit you),
have the following code On Click of the command button:
DoesItExist=dlookup("[IDFi
if isnull(DoesItExist) then
'Open a New Record.
DoCmd.OpenForm "FormA2", acNormal, , , acFormAdd, acWindowNormal
'Copy the Finger print data from FormB to FormA2
Forms![FormA2]![fkAlpha] = Forms![FormB]![fkAlpha]
else
'Open an existing Record based on [fkAlpha]
DoCmd.OpenForm "FormA", acNormal, , "[fkAlpha]='" + Me.FkAlpha + "'"
endif
--------------------------
Please note that this solution is a combination of walterecook's and Nestorio's as well as mine.
jaffer
ASKER
I'm a novice with Access forms, so I dont get the copy forms part(do I do that ahead of time & just reference them?). Also, if isnull(DoesItExist) then nothing needs to displayed (including the finger print data).
Thanks, and I will split the points accordingly.
Russ
Thanks, and I will split the points accordingly.
Russ
Please do it in the sequence I gave you above,
While you are in FormA,
menu bar > File > save as , and give it the name FormA2, then you can do the color changes if you wish, save and close.
Well, if there is No Records, then you will need to Enter the data, and I assume the Finger print scan IS the crucial field, based on which, all the data is identified/related.
When you do it as I mentioned, you don't need to scan the finger print again for the New Record.
But if you don't think it is important, then you can take that line away.
jaffer
While you are in FormA,
menu bar > File > save as , and give it the name FormA2, then you can do the color changes if you wish, save and close.
Well, if there is No Records, then you will need to Enter the data, and I assume the Finger print scan IS the crucial field, based on which, all the data is identified/related.
When you do it as I mentioned, you don't need to scan the finger print again for the New Record.
But if you don't think it is important, then you can take that line away.
jaffer
Hey Russ,
Lunch break for me,
So if I am not arround, I am sure the rest of the guys can take it from here until I come back.
jaffer
Lunch break for me,
So if I am not arround, I am sure the rest of the guys can take it from here until I come back.
jaffer
ASKER
Can I do something like, but not, the following (i'm trying to make this as simple an quick as possible);
DoCmd.OpenForm "Employees", acNormal, , "[fkAlpha]='" + BioCert4.fkAlpha + "'"
This gives 1 record filtered, but doesn't display any data.
Any ideas?
Russ
DoCmd.OpenForm "Employees", acNormal, , "[fkAlpha]='" + BioCert4.fkAlpha + "'"
This gives 1 record filtered, but doesn't display any data.
Any ideas?
Russ
Hi Russ
From where are you giving this docmd.openform command? What is the name of the Form?
You are telling the command, to take BioCert4.fkAlpha info from its field from the Form where this command is executed from,
Does BioCert4.fkAlpha field exist on the Form?
when you execute the command, you are telling it to filter based on BioCert4.fkAlpha,
But when the command doesn't find any matches to [fkAlpha] in the Table, it shows NOTHING,
thus going back to my earlier points.
You better follow the steps in my other posts pal, as this delays your project further.
jaffer
From where are you giving this docmd.openform command? What is the name of the Form?
You are telling the command, to take BioCert4.fkAlpha info from its field from the Form where this command is executed from,
Does BioCert4.fkAlpha field exist on the Form?
when you execute the command, you are telling it to filter based on BioCert4.fkAlpha,
But when the command doesn't find any matches to [fkAlpha] in the Table, it shows NOTHING,
thus going back to my earlier points.
You better follow the steps in my other posts pal, as this delays your project further.
jaffer
ASKER
Thank you for your input. I come up with this solution, from looking at various code. This is only 1 small part of the project, so I am still open to change, just wanted something functional. Let me know what you think, meanwhile I will award the points.
BioCert4.Identify 'Returns fkAlpha iff match exists; else null
If BioCert4.fkAlpha > "" Then
Me.Recordset.FindFirst "[fkAlpha]='" + BioCert4.fkAlpha + "'" ' This appears to correctly populate form
Else
MsgBox "Not Found" ' I would rather zero out the form, maybe display a "NOT FOUND" record.
End If
Respectfully,
Russ
BioCert4.Identify 'Returns fkAlpha iff match exists; else null
If BioCert4.fkAlpha > "" Then
Me.Recordset.FindFirst "[fkAlpha]='" + BioCert4.fkAlpha + "'" ' This appears to correctly populate form
Else
MsgBox "Not Found" ' I would rather zero out the form, maybe display a "NOT FOUND" record.
End If
Respectfully,
Russ
Well Russ,
If it is working for you, then stick to it.
the only change I would recommend is
If isnull([BioCert4.fkAlpha]) =false OR [BioCert4.fkAlpha]<>"" Then
Thanks for the points and the grade.
jaffer
If it is working for you, then stick to it.
the only change I would recommend is
If isnull([BioCert4.fkAlpha])
Thanks for the points and the grade.
jaffer
ASKER
I will encorporate the change.
Thank you for your swift and proper guidance.
Russ
Thank you for your swift and proper guidance.
Russ