Open specfic form if no records found

[cdoyle]

Hi,
I've search the forum, and didn't see this question asked, but I might not be searching for the right criteria.

Here is what I have.

I have a form where the user can enter a user ID, and when they hit submit it will search our user table.

Here is what I want it to do next, if it finds the user ID I want it to open the main entry form so they can enter data for this user.
If it doesn't find the user, I want it to open the 'add new user' form so they can add this user.

I have all my forms, and have my query to search done. I'm just not sure how to make it open the right form depending on the query results?

I'm guessing on the main search form, I'll need some sort of If/Else type code?

 

[neileg]

Two approaches

1) Capture the ID in an unbound control. Use DCount() to find out how many matches there are for that ID. If it is 1, then then the ID exists. If it is 0, it doesn't. Use the result to open the approriate form.
2) Use a combo box to show the valid exsiting IDs set to Limit To List and provide a button to add a new ID that opens your new ID form.

 

[cdoyle]

I think option 1 would be the way to go.

So I've been reading up on DCount, would I put something like this somewhere on my main search form?

DCount("txt_membercheck", "tbl_member", "Member_ID_Number = 1")

 

[neileg]

I would put it in the AfterUpdate event of the control where you capture the user ID. You'll need to wrap this in an If statement in VBA. If you aren't too good on VBA, you'll find option 2 much easier to set up, but a little less elegant.

 

[cdoyle]

I was thinking of the onclick of the button on the search form, it should work there shouldn't it?
My VBA isn't all that great, but am I on the right track with this on the onclick of my button

Dim stDocName As String
Dim txt_membercheck As String

If DCount("txt_membercheck", "tbl_member", "Member_ID_Number = 1")

 Then
      stDocName = "frm_main"
      DoCmd.OpenForm stDocName
    Else
      stDocName = "frm_add_member"
      DoCmd.OpenForm stDocName
    End If

End Sub

I don't think the dropdown would work for this, because there will be so many ID# that it would become an issue trying to find the ID in the list.

 

[neileg]

Your DCount() syntax isn't quite right. I think it should be:

Dim stDocName As String
Dim txt_membercheck As String

If DCount("Member_ID_Number ", "tbl_member", "Member_ID_Number = me.txt_membercheck") =1

 Then
      stDocName = "frm_main"
      DoCmd.OpenForm stDocName
    Else
      stDocName = "frm_add_member"
      DoCmd.OpenForm stDocName
    End If

End Sub

I don't think the dropdown would work for this, because there will be so many ID# that it would become an issue trying to find the ID in the list.

You don't have to search and pick in a combo, you can simply type the value in and it will 'zoom' to the value.

 

[cdoyle]

I tried changing it to what you have above, but I'm not getting a runtime 2001 error.


Any Ideas?

 

[Dreamweaver]

I tried changing it to what you have above, but I'm not getting a runtime 2001 error.


Any Ideas?

Try Removing "me." I always use [] around a field name

Mick

 

[Dreamweaver]

DCount("Member_ID_Number", "tbl_member", "[Member_ID_Number] =" & [txt_membercheck]) =1

I'll get it right for Xmas LOL

 

[cdoyle]

It seems to be getting closer,
Now I'm getting an runtime error 3075
syantax error, missing operator in query expression
'[member_ID_Number]='

 

[Dreamweaver]

It seems to be getting closer,
Now I'm getting an runtime error 3075
syantax error, missing operator in query expression
'[member_ID_Number]='

DCount("Member_ID_Number", "tbl_member", "[Member_ID_Number] =" & Me![txt_membercheck]) =1

Try That

 

[cdoyle]

still not quite working,
If I put a number in the box, I get
runtime error 3464

Just for a test I tried
Criteria that starts with a number, but ends with text
I get runtime error runtime 3075
same as earlier, only now it puts what I put in the text box after the =

If I put just text in the box I get runtime error 2001

Thanks for your help with this, I wouldn't have gotten this far.

 

[Dreamweaver]

So it's a text field treat it as such '" & Me![txt_membercheck] & "'" would have been nice to have the correct information in the beginning and maybe a look at the help files wouldn't go amiss DCount is all you need.