Messy COde? (learning process) (1 Viewer)

Fira_g · Nov 21, 2019

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.

Code:

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 · Nov 21, 2019

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

pbaldy · Nov 21, 2019

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 · Nov 21, 2019

theDBguy said:
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 · Nov 21, 2019

Fira_g said:
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 · Nov 21, 2019

pbaldy said:
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:

Code:

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

isladogs · Nov 21, 2019

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

Code:

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

Fira_g · Nov 21, 2019

theDBguy said:
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 · Nov 21, 2019

isladogs said:
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

Code:

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 · Nov 21, 2019

Attached the file.

isladogs · Nov 21, 2019

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

Code:

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 · Nov 21, 2019

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

with this line of code

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 · Nov 21, 2019

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

with this line of code

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 · Nov 21, 2019

theDBguy said:
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 · Nov 21, 2019

Fira_g said:
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 · Nov 21, 2019

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

Yes, that is not what I meant )

theDBguy · Nov 21, 2019

Fira_g said:
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 · Nov 23, 2019

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.

Messy COde? (learning process) (1 Viewer)

Registered User.

I’m here to help

Wino Moderator

Registered User.

Attachments

I’m here to help

Registered User.

Attachments

MVP / VIP

Registered User.

Registered User.

Registered User.

Attachments

MVP / VIP

Registered User.

I’m here to help

Attachments

Registered User.

I’m here to help

Registered User.

I’m here to help

Super Moderator

Similar threads

Users who are viewing this thread