Messy COde? (learning process)

[Fira_g]

What I am trying to do: when I type a name in FieldID (combobox) and it is not in the there I click the button. As i still have the text I was typing in the combobox I want for the code to check if the FieldID is not empty and not equal to the value in FieldName in tblMoreFields which is string. If not then open the frmMoreFields form with text I started typing copied to the FieName of that form
It works for the If and Else part but errors on the Elseif statement.

Private Sub AddField_Click()
If IsNull(Me.FieldID) Then '
    DoCmd.OpenForm "frmSomeFields", , , acDialog, acFormAdd
    Me.FieldID.SetFocus
ElseIf Not IsNull(Me.FieldID) Then
    Dim FN As String
    FN = Str(Me.FieldID.Value)
    If FN <> "tblMoreFields!FieldName" Then
    DoCmd.OpenForm "frmMoreFields", , , "[FieldName] = " & FN, , acDialog
    End If
    
Else
    DoCmd.OpenForm "frmMoreFields", , , "[FieldID] = " & Me![FieldID], , acDialog
    Me.FieldID.SetFocus
End If
End Sub

What is wrong with the code/

 

[theDBguy]

Hi. What does the error message say? Also, is the Limit To List = Yes or No?

 

[pbaldy]

The Else will never be executed. Me.FieldID can either be Null or Not Null, thus either the If or the ElseIf will always run.

 

[Fira_g]

Hi. What does the error message say? Also, is the Limit To List = Yes or No?

My bad, no error, if it is not in the list, it just says that not in the list offering to open the Form to add.

 

[theDBguy]

My bad, no error, if it is not in the list, it just says that not in the list offering to open the Form to add.

Okay, I think that error message is from the Not In List settings. Did you want the user to enter an invalid data and then click a button to validate it? If not, why do you have a button for adding new data?

 

[Fira_g]

The Else will never be executed. Me.FieldID can either be Null or Not Null, thus either the If or the ElseIf will always run.

Oh I c. That's why it gives me mismatch error when I chose existing value from the list and click the button pointing to this line of the code:

DoCmd.OpenForm "frmMoreFields", , , "[FieldName] = " & FN, , acDialog

 

[isladogs]

In the ElseIf part (which should just be Else), you have FN = Str(Me.FieldID.Value)

so you need text delimiters in the OpenForm line

DoCmd.OpenForm "frmMoreFields", , , "[FieldName] =[B][COLOR="Red"] '[/COLOR][/B]" & FN[B][COLOR="red"] & "'"[/COLOR][/B], , acDialog

 

[Fira_g]

Okay, I think that error message is from the Not In List settings. Did you want the user to enter an invalid data and then click a button to validate it? If not, why do you have a button for adding new data?

Lets say I have 2 tables:
1. tblMoreFields with FieldID as PK and a FildsName
2. tblSomeFilds
tblSomeFilds has the FieldID as FK

on the frmSomeFields the FieldID is a combobox. If it type a "Macaroni Cheese" there and it is not in the list I click ta button and the code will take that "Macaroni Cheese", open frmMoreFields and copy/insert/transform it to the FildsName on that form as a string, because I already typed it on the previous form

 

[Fira_g]

In the ElseIf part (which should just be Else), you have FN = Str(Me.FieldID.Value)

so you need text delimiters in the OpenForm line

DoCmd.OpenForm "frmMoreFields", , , "[FieldName] =[B][COLOR="Red"] '[/COLOR][/B]" & FN[B][COLOR="red"] & "'"[/COLOR][/B], , acDialog

Changing it to Else gives me syntax error.
Added text delimiters, now even if the value in the list it seems to execute the IF statement opening frmSomeFields as acFormAdd

 

[Fira_g]

Attached the file.

 

[isladogs]

I'm not at all clear what you are trying to do but this is what I meant

Private Sub AddField_Click()
If IsNull(Me.FieldID) Then 
    DoCmd.OpenForm "frmSomeFields", , , acDialog, acFormAdd
    Me.FieldID.SetFocus
Else
    Dim cID As String
    cID = Str(Me.FieldID.Value)
    If cID <> "tblMoreFields!FieldName" Then
    DoCmd.OpenForm "frmMoreFields", , , "[FieldsName] = '" & cID & "'", , acDialog
    End If
End If
End Sub

 

[Fira_g]

I seem to have uploaded the file without the change you suggested.

with this line of code

    DoCmd.OpenForm "frmMoreFields", , , "[FieldID] = " & Me![FieldID], , acDialog
    Me.FieldID.SetFocus

i wanted the other form to be opened to the existing record if I wanted to modify it.

 

[theDBguy]

I seem to have uploaded the file without the change you suggested.

with this line of code

    DoCmd.OpenForm "frmSomeFields", , , "[FieldID] = " & Me![FieldID], , acDialog
    Me.FieldID.SetFocus

i wanted the other form to be opened to the existing record if I wanted to modify it.

Hi. Not sure if this is what you want but take a look and let us know.

 

[Fira_g]

Hi. Not sure if this is what you want but take a look and let us know.

Made mistake in the last Else statement on my first post. Corrected to as it is on my correct test db.

The code from my last post works on its own. I just want the FieldID to be tested for conditions I described and
open frmMoreFields to a new blank record
or to a new record with FieldName with the text I typed copied to FieldName field on the frmMoreFields if it is new not on the list
or if the value exist on the tblMoreFields ==> open frmMoreFields to that record

I think I should have named my tables, forms and fields with better names, I am starting confusing myself with all these names

 

[theDBguy]

Made mistake in the last Else statement on my first post. Corrected to as it is on my correct test db.

The code from my last post works on its own. I just want the FieldID to be tested for conditions I described and
open frmMoreFields to a new blank record
or to a new record with FieldName with the text I typed copied to FieldName field on the frmMoreFields if it is new not on the list
or if the value exist on the tblMoreFields ==> open frmMoreFields to that record

I think I should have named my tables, forms and fields with better names, I am starting confusing myself with all these names

Hi. Was that a "no" (to my question)?

 

[Fira_g]

Hi. Was that a "no" (to my question)?

Yes, that is not what I meant )

 

[theDBguy]

Yes, that is not what I meant )

Sorry, I'm still confused. Did you try the file I posted and what was wrong with it?

 

[Pat Hartman]

Are you sure that you shouldn't be using the NotInList event. That is normally what you want to use if you enter something in a combo or list that isn't there. You can specify a form that Access should open and that will allow you to add the new item.

Personally, I prefer to not allow users to enter new options on the fly. I find that they are sloppy and can't spell so way too many typos get added. My preference is to to only allow admins to add items to lists because they are more likely to do it correctly.