What data type is SSN?
If it is text, then the syntax on this line should be:
stLinkCriteria = "[SSN]=""" & Me![SSN] & """"
That should open the second form to to record that matches than SSN if a
record exsits in table2 for than SSN. But since you say there cannotbe more
than one record in table2 for an SSN, I don't know why you need to try to
find a different record for the auic and bsc fields. Maybe I did notgive
the correct answer the first time.
Are you trying to create a record in table 2 for the SSN or does the record
alreadys exit? If it does not exist and you are trying to create one, then
the Where criteria you are trying to use will not work.
If you can describe the process, I think I can give you a better answer..
martinmike2 said:
How are you linking the SSNs between the forms?
Can a person in table1 have more than one record in table2?
Does the first form open the second?
--
Dave Hargis, Microsoft Access MVP
:
You are part of the way there. Nothing is happening because you haven't told
it to do anything.
To start with, you need to add code to the After Update event of cboAUIC so
it will requery cboBSC when you select a row in cboAUIC:
Private Sub cboAUIC_AfterUpdate()
Me.cboBSC.Requery
End Sub
Then you need to use the After Update of cboBSC to navigate to the record
that matches the values selected in the two combo boxes:
Private Sub cboBSC_AfterUpdate()
With Me.RecordsetClone
.FindFirst "auic = " & Me.cboAUIC & " AND bsc= " & Me.cboBSC
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Note. The above "air code" assumes auic and bsc are both numeric fields in
your table. If both are text, you would use this syntax:
.FindFirst "auic = """ & Me.cboAUIC & """ ANDbsc = """ & Me.cboBSC
& """"
--
Dave Hargis, Microsoft Access MVP
:
Ok, I have searched and serached and can't find a decent solution to
my current issue.
I want to assign a unique ID via a modal pop-up form. The form has 2
combo boxes to refine the data (second combo box is filtered by the
first combo box) and 2 text boxes to show the remaining data contained
in the record.
Now, I can select the values in the combo boxes but the text boxes
aren't filling as i hoped. I dont have any queries or codebehind the
text boxes but the SQL behind the combo boxes is:
cboAUIC: SELECT DISTINCT tblAMD.auic FROM tblAMD
cboBSC: SELECT DISTINCT tblAMD.bsc FROM tblAMD WHERE
(((tblAMD.auic)=[Forms]![frmASGNAMD].[cboAUIC].[value]));
If this is incorrect or just plain dirty, help would be appreciated..- Hide quoted text -
- Show quoted text -
ok, i used your code and the form works when i open it by itself,but
when i call it with a cmd button i get a "current record does not
exist" error.
I am using the form to assign a record to an individual via a SSN
field. The form is called from a command button on the main form and
is linked to the SSN in the recordset that the main form is using..
I'm sorry if this is confusing, let me try to explain it a different
way.
TABLE1:
NAMELAST
NAMEFIRST
SSN
TABLE2:
SSN
AUIC
BSC
BIN
TITLE
the main form uses table1 and the pop-up uses table two with the SSNs
linked.- Hide quoted text -
- Show quoted text -
the code behind the button is:
Private Sub cmdAssign_Click()
On Error GoTo Err_cmdAssign_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmASGNAMD"
stLinkCriteria = "[SSN]=" & Me![SSN]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdAssign_Click:
Exit Sub
Err_cmdAssign_Click:
MsgBox Err.Description
Resume Exit_cmdAssign_Click
End Sub
but i just looked at it again and it's not actually linking the SSNs.
The person in table 2 cannot have multiple records in table 2.
the command button that opens the form is on the main form.- Hide quoted text -
SSN is a number/Long Integer, formatted as a Social Security Number
ok, ill give it a shot. tblPERS is the main personnel table with the
personal information of each employee. tblAMD is a manpower table
with unique codes to describe each employees position, job, and
specialty. the AMD data is derived from a monthly report we intend
to
"merge" on a monthly basis.
the main form, frmPERS, is driven off tblPERS with various subforms
driven off queries of various other tables. Under the AMD tab there
is a subform that displays the employee's AMD data and a command
button to "Assign" AMD data to a new employee. The Assign command
button calls a form, frmASGNAMD, so we can assign the employee's SSN
to a record. The record already exists in tblAMD it just needs the
SSN added to the SSN field.
On frmASGNAMD there are 2 combo boxes and two text boxes. cboAUIC is
used to narrow down cboBSC's choices so we don't have to scroll
through 1000's of unique BSC's. The end-user would first select an
aUIC from cboAUIC which forces cboBSC to display only those BSC's
attached to that particular aUIC. Once the BSC is selected, there is
only one BIN and TITLE associated with that BSC, the remaining text
boxes, txtBIN and txtTITLE, are auto-filled with those values
narrowed
down by the values in cboAUIC and cboBSC. On frmASGNAMD there is
another command button, cmdASSIGN, that places the SSN of the
employee
in the SSN field of tblAMD. The employee's AMD Data is henceforth
displayed in a subform on frmPERS until re-assigned.
I hope that clears up the process a bit. If not let me know and ill
see if I can clarify a little better.
Thanks for putting up with me on this, im not the best with Access,
but im the only one who has at least a little knowledge of its
innards.- Hide quoted text -
- Show quoted text -
