help with nested if and open a certain form based on field data

[krowe]

Hi

I'm not able to explain easily my whole database, however the essential info i think is, i have a table that inlcludeds selected summary data from 4 other tables. there is a field to identify which table the records come from "type".

I have a search form and would like to open one of 4 forms depending upon what the value of the type field is.

here is my code:

Private Sub Command25_Click()
Dim strFrmName As String
Dim strIDField As String
If Me!Type = "Lender" Then
        strFrmName = "frmLenders"
        strIDField = "LenderID"
ElseIf Me!Type = "Agent" Then
        strFrmName = "frmAgent"
        strIDField = "AgentID"
ElseIf Me!Type = "Service" Then
        strFrmName = "frmContactDirectory"
        strIDField = "ID"
Else
    strFrnName = "frmLandlord"
    strIDField = "LandlordID"
End If
DoCmd.OpenForm strFrmName, acNormal, "", Me!ID = strIDField, , acNormal
End Sub

it seems to open the correct form, but it doesn't go to the correct field (as you can tell from the code, each table has a differently names unique identifier)

Thanks for your time

Kev

 

[swmorin]

whats name of the ID field on your forms? is it ID?

If it is I would name it something more meaningful and then add the docmd.openform in each of the if statements

 

[krowe]

Hi

The unique identifier on my combined form is called ContactID, however there is a field called ID which contains the Unique Identifier of each record in it's original table. This is the field i am trying to use to find the correct record when the main form opens, but it is not working.

I will try to put the openform command in the if statement, however i thought the way i have it would be a bit tidier.

Thanks for your help, i dont have time now, but will let you know how i get on tomorrow am.

thanks

Kev

 

[vbaInet]

DoCmd.OpenForm strFrmName, acNormal, , "Me!ID = " & strIDField, , acNormal
End Sub

Notice what's done above.

Also, is the criteria field on all the forms called ID? And are they all Numeric? If not, you will need to handle that in your IF...ELSE IF block.

 

[krowe]

Hi

Thanks for your reply, i have made the adjustment and seem to be getting further, however i get a prompt to enter the ID number, which i dont think i should be as it should be picking it up from Me!ID (the button that the code sits on is on a form which contains the field called ID, the strIDfield identifies the name of the field on the corresponding when it opens).

I have checked that they are all number fields and they are.

Here is my current code:

Private Sub Command25_Click()
Dim strFrmName As String
Dim strIDField As String
If Me!Type = "Lender" Then
        strFrmName = "frmLenders"
        strIDField = "LenderID"
ElseIf Me!Type = "Agent" Then
        strFrmName = "frmAgent"
        strIDField = "AgentID"
ElseIf Me!Type = "Service" Then
        strFrmName = "frmContactDirectory"
        strIDField = "ID"
Else
    strFrnName = "frmLandlord"
    strIDField = "LandlordID"
End If
DoCmd.OpenForm strFrmName, acNormal, , "Me!ID = " & strIDField, , acNormal
End Sub

Thanks again for your help so far

Kev