Public Variable and updating form

[poporacer]

I have 2 problems with my DB I have been trying to resolve.

Problem 1: I have a form (frmEdit) that has a combo box (cmbCDC2) on it. I have the NotInList property set to open a new form (frmAddInmate) to add the new data. I have set a Public boolean variable (flgNil). When frmAddInmate is opened, the value is set to False. If data is added, then flgNil is set to true and the form closes. The problem is that when the program returns to frmEdit flgNil is always False. For some reason the variable is not working like I want it to.

Problem 2: On frmEdit I have a routine to check if data has been added (flgNil = true). The routine isn't working properly. What I want to do is to return to the form and have cmbCDC2 populated with the new data (requery?) and with the data entered as the current item, then the focus set to txtIncidentDate and the current record set to the criteria based on the form (cmbLogNum and cmbCDC2)

I hope I made this clear. Thanks in advance for the help.

 

[RuralGuy]

Open your frmAddInmate form in dialog mode and then set
Response = acDataErrAdded
...in the line after the OpenForm. This causes the ComboBox to requery.

 

[ajetrumpet]

And, for the first problem, I would assume the default value for Boolean variables is FALSE? It would appear that it is that way. Scrap the two duplications of the public variable behind both of your forms, and add a global module:

global flgnil as boolean

The Boolean is being reset to FALSE because you have it listed as a Public behind each form...

 

[poporacer]

Well, first to Rural Guy. I did open the form in acDialog mode and put in the Response = acdataErrAdded but this did not work properly. And for ajetrumpet. I thought that declaring the variable would reset it but that it ony resets upon the form opening and should have worked...Creating a module and declaring the Variable there worked great.

So this is where I am at now...When I add the new data on frmAddInmate and return to frmEdit, I need the form to update with the new data...I hope this makes sense. I will try to clear it up.
I have 2 tables, one contains the data for incidents, there will be several incidents with the same log number with data for a different person in each record.
The other table is for the people with an ID number associated with that person.

frmEdit has 2 combo boxes, the first combo box (cmbLogNum) is populated by a distinct query. The second combobox (cmbCDC2) is populated from a query based on cmbLogNum and frmEdit is populated based on the matching record based on the 2 combo boxes. If I want to add a new person to a selected incident (cmbLogNum) I have the NotInList event for cmbCDC2 open a new form (frmAddInmate) to input the new record and then return to frmEdit. When it returns, I need cmbCDC2 to be the new record. (this might not be the data that was typed in the combobox that generated the NotInList event) and the form updated with the proper record.
I hope in trying to explain, I didn't create more confusion...
Thanks

 

[RuralGuy]

...(this might not be the data that was typed in the combobox that generated the NotInList event)...

If this is the case then the:
Response = acDataErrAdded will not address the issue. How about posting all of the code you have in this NotInList event so we can take a look at it?

 

[poporacer]

Here is the NotInList code:

#Private Sub cmbCDC2_NotInList(NewData As String, Response As Integer)
Dim Reply As String
Dim Temp As String

Reply = MsgBox("Do you want to add this CDC Number to this incident?", vbYesNo, "Confirm Add new record")
    
    If Reply = vbYes Then
        DoCmd.OpenForm "frmAddInmate", acNormal, , , acFormAdd, acDialog, NewData
        
        ' Check if new entry was added in the frmAddIncident Form.
       
        If flgNil = False Then
            'Not added so undo
            Me!cmbLogNum.Undo
            Response = acDataErrContinue
        Else
            ' New entry has been added.
            Response = acDataErrAdded
            Me.cmbLogNum.Requery
            Me.cmbCDC2.Requery
            Me.txtIncidentDate.SetFocus
        End If
    Else
        ' Not added so undo
        Me!cmbLogNum.Undo
        Response = acDataErrContinue
    End If
End Sub

 

[RuralGuy]

Is flgNil some global or public variable that is set by the frmAddInmate form?

 

[poporacer]

Yes, flgNil is a global variable set in a module used to determine if a new record was entered.

 

[RuralGuy]

It is a little difficult getting my arms around your form interaction. Is it really necessary to requery the cmbLogNum ComboBox in this NotInList event? It appears that the cmbCDC2 ComboBox is dependant on the cmbLogNum ComboBox but not the other way around. Is the txtIncidentDate control the next control in the form's tab order?

 

[ajetrumpet]

Allan,

It would be a bit more helpful for you to download the database he posted initially. I had to do that in order to get an a full picture of what he was doing...

 

[gemma-the-husky]

i know youve spent ages on this

i see you declaed flgnil as a variable in the popup form - now this wont work - as soon as you close the form, the variable is scrapped - so you are likely to get an error referencing it when you return from the popup form.

[edit]
in fact looking at at it again, you SHOULD have got an error when you returned from the popup. so I looked a bit more, and I see that you have also declared flgnil in the frmedit form, which compounds the problem. Variables have cetrtain scope characteristics, so the form frmedit will use the variable flgnil from the frmedit itself, which is only ever false.

When you open the popup form, frmaddinmate then has its own copy of flgnil, which it uses, but as I say, this is discarded when it closes.

[end edit]

what you need is a code module (the last tab, after reports and macros) and declare flgnil in there. Delete the variables in both the other forms, and just use the one in the code module

hope this makes sense

nice looking form by the way

 

[ajetrumpet]

i see you declaed flgnil as a variable in the popup form - now this wont work - as soon as you close the form, the variable is scrapped - so you are likely to get an error referencing it when you return from the popup form.

[edit]
in fact looking at at it again, you SHOULD have got an error when you returned from the popup. so I looked a bit more, and I see that you have also declared flgnil in the frmedit form, which compounds the problem. Variables have cetrtain scope characteristics, so the form frmedit will use the variable flgnil from the frmedit itself, which is only ever false.

When you open the popup form, frmaddinmate then has its own copy of flgnil, which it uses, but as I say, this is discarded when it closes.

[end edit]

what you need is a code module (the last tab, after reports and macros) and declare flgnil in there. Delete the variables in both the other forms, and just use the one in the code module

This has all been covered already, Gemma.

 

[gemma-the-husky]

where was it covered - i couldnt see it

poporacer said

Yes, flgNil is a global variable set in a module used to determine if a new record was entered.

but that wasnt quite right - he had declared the same variable twice in form level modules, and not in a code module which is where it needs to be

 

[ajetrumpet]

where was it covered - i couldnt see it



but that wasnt quite right - he had declared the same variable twice in form level modules, and not in a code module which is where it needs to be

That's because you probably looked at the original file he posted (i mean, he's only got ONE on here). I saw that too, and made my comment. I assume, because of his initial response to my post:

And for ajetrumpet. I thought that declaring the variable would reset it but that it ony resets upon the form opening and should have worked...Creating a module and declaring the Variable there worked great

he has fixed that. At least I hope so!

 

[RuralGuy]

Allan,

It would be a bit more helpful for you to download the database he posted initially. I had to do that in order to get an a full picture of what he was doing...

Thanks Adam. I got so deep into this thread that I completely forgot the OP had posted a db to look at.

 

[gemma-the-husky]

so you did, shame i wasted all that time as well then - lol

time for bed I think

 

[ajetrumpet]

so you did, shame i wasted all that time as well then - lol

time for bed I think

Yes, I think.

 

[RuralGuy]

PopoRacer,
I'm somewhat concerned about your table relationships. Logic tells me that there can be a 1:m relationship between an inmate and an offense. One inmate can commit many offenses. This would dictate that each record in the tblOffense have the InmateID as a ForeignKey. Then you could easily show all of the offenses committed by the inmate. Instead, you have the two tables tied together with something called CDCNum as if CDCNum is the most important element of your system. Because this field is allowed to be duplicated in both tables, Access has no idea what relationship exists between the two tables. Would you tell us what this field represents and what sort of relationship exists between your two tables please?

 

[poporacer]

Rural Guy you are pretty close there.
tblOffense is used for the offenses, there can be many offenses with the same log number (one for each inmate involved in that incident Log Number) and yes one inmate can commit many offenses.
tblInmate is for the data for each inmate. CDCNum is a unique number to identify each inmate (no duplicates)

Here is the most current DB with the suggested changes.

Thanks for all your help!

 

[RuralGuy]

It appears that the CDCNum is a number that you assign that is unique for each inmate. I would suggest in your design that you put the ID field (which I would rename InmateID) in the tblOffense as a ForeignKey rather than the CDCNum. You can easily show the CDCNum whenever you want with a query join and Access will then know what sort of relationship these two tables have and can enforce referential integrity for you.