Dialog box

[Meena]

Hi everyone, I am new to using access and have so many problems designing and implementing this database,,,,,anyways, here is my problem:
I want to create a dialog box such that as soon as a user clicks on "enter new user" command button, a dialog box opens up asking for user ID(primary key). First I want to check and see if the user ID already exists in the table, and IF not then open up an Insert New User Form. If the User Id already exists, then I want to display all the information about the user thats related in all 4 of my table. I hope someone out there understands this problem and helps me out. thanks in advance.

 

[jwindon]

If the user ID IS truly set as a primary key in your table, the user should NOT be able to enter a userID that already exists. Make sure your primary key index is set to Indexed: Yes, No Duplicates.

Make sure your EnterUserID control is UNBOUND. Have a button on your form that runs opens a form which is built off a Select Query. This query is built off of the table that contains the information you would like to show about the userid if there is a match.

The criteria for the query should be set to Criteria: =Forms!NameofTheSearchform![UserID]

If the user already exists. If the user does not exist the form will come up blank.

You can add code to the command button that will count the number of records found if the query returns zero results to open a different form if you need that.

This is all a lot to absorb, but take your time and you will do fine.

 

[Meena]

Thank you Thank you Thank you

It worked. Now How would I create a form that opens up if no mathches of the record were found, just for the Add Mode.

 

[jwindon]

Right click on the command button and put this code on the OnClick event...

Dim QueryCnt As Integer
Dim NoMatchChoice As Integer

QueryCnt = DCount("*", "tblUserIDs","[UserID] = " & Me.UserID")


If QueryCnt = 0 Then

NoMatchChoice = (MsgBox("User ID not found. Enter new user?", vbYesNo, "NO MATCH"))

If NoMatchChoice = vbYes Then
DoCmd.OpenForm "frmInsertNewUser", acNormal, , , acFormAdd
Exit Sub
Else
Exit Sub
End If
Else
DoCmd.OpenQuery "qryQueryName", acViewNormal, acEdit
End If


Please forgive me if it doesn't work the first try. I typed quickly as I am trying to get out of the office right now.

You will need to look thru and make sure to put in the proper table name and query name where applicable.

[This message has been edited by jwindon (edited 11-09-2001).]