What type of box to use

[armyofdux]

Hello everyone,



Trying to make/design a form for adding a persons Social Security Number. I have existing data within my database as a table. I can get everything to work properly with a form for adding new data except for this particular field. The data and where it originates from constantly changes but it does not drop off existing data. I guess the problem is that I obviously cannot have a duplicate entry.

So I want to be able to have my data entry persons add the students via this form but like I said I cant have duplicate entries. Ideally I want to have the SSN field at the top so that when a new record is about to be entered and the ssn is already in the database that it pulls up the record already in existence? Someone point me in the right direction?

[June7]

Options:

1. UNBOUND combobox to enter/select SSN, if SSN is already in list then filter form to that record, if not in list then go to new record row - code can populate the SSN field with combobox value

2. BOUND textbox to enter SSN, code in BeforeUpdate event looks for existing record and if found, cancels input and filters (or go to) that record

[Missinglinq]

As usual, there are almost always multiple ways to accomplish a task in Access. In addition to June7's suggestion, you could use something like this:

Private Sub SSN_AfterUpdate()

    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[SSN] = '" & Me![SSN] & "'"
    If rs.NoMatch Then
     'Do Nothing Continue entering New Record
    Else
     Me.Undo
     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End If

End Sub

This assumes that your Social Security Number field is defined as Text, which it usually is, and you'll need to substitute your actual name for SSN in the code, above.

Linq ;0)>

[armyofdux]

Thanks guys, it gave me some ideas and I like all of them. I can't figure out how to implement it. Could I sanitize the SSNs and upload it here? I like learning just need someone to show me how. Any and all help would be appreciated.

[Missinglinq]

...Could I sanitize the SSNs and upload it here...

Sure!

Linq ;0)>

[armyofdux]

Attachment 19476 Here it is. The end state of this one is to assign a class to a respective student as well as a room assignment. I have gotten pretty far (at least I think so) in the creation of this without having been in Access as much as I need to be.

[June7]

I tried opening the db and get error 'unrecognized format'.

[armyofdux]

DBHOME.Zip Try again

[June7]

HUB Table has all 10 fields set as compound primary key. Could just define them as a compound index.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be RoomNum or Room_Num.

Can a soldier have more than 1 MOS? Certainly can't have more than one GENDER. So why is GENDER in HUB Table? This belongs in Student Data. Why is booking info (room #, arrival date) in Student Data?

Can a soldier attend more than 1 class?

Do you want to keep history of soldier course attendance and bookings? If so, this info does not belong in Student Data. Student Data should have info about the individual that is not likely to change or can change but don't care about history of that data element (Last, First, Middle, Gender, DOB, Phone, Address, Rank, Grade, DateOfRank, etc).

Should Registar be spelled Registrar?