SQL/Listbox/Textbox issue

[poporacer]

I have a form that has cascading listboxes. The first one lists incident numbers and the second one is for names that are associated with the selected incident number. The form has textboxes that are populated via code from the query on the second textbox. I have a command button to add incident numbers. When this button is clicked, a form opens up and you input the information and then the form closes. The new incident number is there and the name is there but when you select the name to input the pertinent data, the textboxes on the form get populated with data....(apparently from the first record in the table) The textboxes should be blank. If I close the form and reopen it, the boxes are blank so it appears that the query is working properly. Here is a snippet of the code I use.

Private Sub lstCDCNum_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone

rs.FindFirst "[ID] = " & Str(Nz(Me![lstCDCNum], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.txtConvictDate = rs![Conviction Date]
Me.txtCourtComments = rs![CourtComments]
Me.txtDAAccept = rs![Date DA Accepted]
Me.txtDACaseNum = rs![DA Case Number]

when I look in the code window it shows the recordsets are null, but the textboxes get populated with data! Any suggestions?

 

[wazz]

you say the cascade is from incident number -> name, but your code is requerying the form from the first list. shouldn't it do this from the second (name) list? (also, after adding the new data on your popup form are you requerying both lists on the original form and the original form? the form might be setting itself to the first record until you select something from the name list...hard to tell.)

 

[poporacer]

Maybe I should give more information. I have two tables- tblOffense and tblName. tblOffense has all of the pertinent information. There are several records in tblOffense that have the same LogNum (one for each name). The first listbox is populated with a DISTINCT query of tblOffense. The second listbox is populated when a LogNum is selected in the first listbox. This will populate the second listbox with all the names associated with the selected LogNum. When you select the name, I want the form pupulated with the data from tblOffense that matches the LogNum and name.

Maybe this will clear it up

tblOffense

ID LogNum NameID IncDate Charge labor
1 D001 12 2/2/08 23.0 12
2 D001 18 2/3/08 12.0 8
3 D001 34 2/5/08 4.0 20
4 D002 18 2/4/08 8.0 12

tblName

ID Name Rate Level
12 Smith 1.0 3
18 Jones 1.5 2
34 Adams 1.2 3


So in the First listbox it will show 2 LogNums... D001 and D002. When you select LogNum D001 the second Listbox will populate with the names associated with the Name ID (right Join with tblOffense and tblName)

Then when you select a name in the second listbox the forms textboxes get populated with the data from tblOffense for the match (LogNum and Name)

I have a routine to add a logNum...a new form opens up to enter the LogNum and then closes when complete and the first textbox is requeried...This works and the associated Name is in the second textbox. When you select this name, it is supposed to populate the textboxes with the matching query (Supposed to be Blank until you enter data) What happens is it populates the form with data from the first record (not the matching record) If the form is closed and reopened the query works correctly. I hope this clears it up a bit.

 

[wazz]

still very difficult to tell where the problem is coming from without seeing it. still sounds like something is not being requeried (or at the wrong time). i was trying a few things and one thing i forgot to do was include the NameID in the second listbox (cbobox for me). since the form (tblOffence) uses NameID i had to include the NameID - not just the name - in the second list. but you say that's working... if the form works after closing and opening then it almost has to be a missing requery (or query problem). i'd really need to see it to save making unhelpful suggestions. (also, when you say you add a new log num, are you adding a new record to tblOffence, with all necessary data?)

 

[poporacer]

Here is the Database...to duplicate the problem do this...Click on add incident, fill in the controls on the new form that opens andthen click on"Add Incident" . Then select the new incident you added and then the name in the second listbox....the data gets filled in when it should be blank!

 

[wazz]

'fraid i can't make it go.
can i interest you in a redesign? did you remove many tables? looks like there should be a lot more and the way it's set up will cause a lot of grief i should think. sry. either someone else can step in here, or, consider a new thread to get ideas on changing the table structure. or ask here in this thread.

 

[poporacer]

This is the database, I did not remove any tables. What did you mean by a redesign?

 

[wazz]

several items in tblOffense should be in their own table:
- tblPenalCode
- tblCharge (homicide, assault, drug, weapon, escape, etc.); could be the same as penal code?
- ethnicity

- looks like you want to be able to add more than one person to an incident. this requires another table: tblIncidentSuspect (or other name).
- the ID field in a table should not be called ID; should be IncidentID or PenalCodeID, etc.

- each table should hold data about only one subject. that's why tables are usually named with a noun (tblContact; tblBusiness; tblProduct; etc.). it's usually singular (Contact not Contacts) because, technically, it refers to a single row in a table. some people use plural (tblContacts) and that's usually OK. either way, one subject matter only in each table.
- if you have groups of related things in a table it usually means they should be in their own table. homicide, assault, drug, weapon, escape, etc. are all "charges" and should be separated. when you apply one (or more) of them to an incident you don't type it in each time, you simply look at the list you have (tblCharge) and pick one (or more).

hope that helps get you going. is this your own db or something for work? are you allowed to change it?

 

[poporacer]

This is my own DB I am trying to make for work...but I don't think it would be beneficial to have all those tables...tblEthnic...would only have 4 entries and I don't think the overhead for the table would not ofset the benefit of having a dedicated table. TblCharge, to list all the possible charges would be huge as well as tblPenalCode...the info put into tblOffense will be small...yes there might be some duplications but I don't see the benefit af creating a foriegn key to identify a small data item. i am open for suggestions....
So you suggest one table for incidents, one table for names and then a table for suspects that will have a suspect name, Offense Log#, and offense data....one suspect can be involved in more than one offense....if this is what you are suggesting, it appears that the data is still duplicated but in a different manner.

 

[wazz]

This is my own DB I am trying to make for work...but I don't think it would be beneficial to have all those tables...tblEthnic...would only have 4 entries and I don't think the overhead for the table would not ofset the benefit of having a dedicated table.

the benefits of separate tables always outweigh the drawbacks, if there are any. the reason for separate tables is very *very* simple. even if you ultimately only have 4 entries you may have to enter it 50, 100, 1000 times. you can do that, or enter it ONCE in its own table and select it from a combobox or listbox when you need it. (you are not reentering the data when you do that you are picking it out of another table). also, if you do have to enter it for every person and you end up with 500 people, then you run the risk of entering 'white', 'white', 'whit', 'whte', or one day you enter a short-form 'wht'. there you have two typos and a short-form. then if you need to search for all white people you will only find the ones that match your search criteria (if you enter "white" you find matches for that). your search will not give you what you want or expect. separate tables for everything in which you enter your data ONCE and then "look it up" when needed is the reason to use a database.

TblCharge, to list all the possible charges would be huge as well as tblPenalCode...

even more reason to only have to enter the data only once and reuse it instead of retyping it.

So you suggest one table for incidents

yes (are incidents and offences the same?)

one table for names and then a table for suspects

i would think that names and suspects are the same.?.

that will have a suspect name, Offense Log#, and offense data....

offence log number and data go into...tblOffence - not with names/suspects. then you connect the people involved to the offence. if only one person may be involved then you just need a combobox to select a name from.

one suspect can be involved in more than one offense....if this is what you are suggesting, it appears that the data is still duplicated but in a different manner.

EXACTLY right. it is duplicated by copying the necessary ID into the relevant table - not the entire row of data. for example, an Offence (each offence will have a (unique) primary key of OffenceID) will need a person associated with it. so you look in tblPerson (each person will have a (unique) primary key of PersonID) and "pick" one to put into tblOffence. the PersonID that is now in tblOffence is a "foreign key". why? because it is a key but from another table. that is the "duplicate" you refer to.

still wondering if the opposite is true: can one offence involve more than one person. hmmm, i suppose one offence is linked to one person, but an incident might involve many? if that's true which one do you want to connect people to? incidents or offences? are they different? do you need to track both or just one?