Multiple problems on new database

Jaysc

New member
Local time
Today, 06:13
Joined
May 8, 2007
Messages
6
Now, I'm not a (complete) newbie when it comes to Access, but this is stumping me to the point that my head hurts from the desk banging. :p

There are two tables in the database, tbl_minerID and tbl_collection with a one-to-many relationship from tbl_minerID to tbl_collection. tbl_minerID contains a column populated by autonumber and a name column (tied to cbo_name on the form). The form allows you to type in a record for various values, which then ties that entry to the minerID so that only those records display for the name selected in cbo_name.

At least that's how it used to work. Now, no matter what I do, I get a type mismatch error in my VBA code (copied exact from the working copy, with names changed) which directs me to: rs.FindFirst "[MinerID] = " & Str(Nz(Me![cbo_name], 0)) Totally stumped on this, and hoping someone can point me in the right direction. Advance thanks for any assistance you can give :)

Database attached if anyone wants to look at it.
 

Attachments

You are setting a string the NZ to a number. Try:

.FindFirst "[MinerID] = '" & Str(Nz(Me![cbo_name], "0") & "'" )

But, if MinerID is a number then:

.FindFirst "[MinerID] = " & Nz(Me![cbo_name], 0))
 
The MinerID is an autonumber, yes. The name field in tbl_minerID is what is suppose to show up in the cbo_name combobox.

Tried your fix(es), but I'm still having issues.

Doing the first fix, I find that it will enter the name in, but I now wind up with a whole slew of errors and problems. It seems that the autonumber field for the previous entry gets added to the name list. Also, adding a second name works, but when I try to click elsewhere I get "Update or CancelUpdate without AddNew or Edit." and all my names turn to numbers.

Second fix: Compile error: Syntax error which then highlights the code I typed in the first post.
 
*sighs* Found out the issue. I had bound the combobox to Name, so erasing that and turning it into an unbound box has fixed up ALL of my problems, except a persistant one that dates back to my original DB.

When I enter a new name in the combobox, it assigns the records of the first name. Editing those values edits the record for the first name. There a way that I can VBA code it so that it will always provide a fresh record instead of pointing to the records of the first name entry?
 
Your database has a few more issues than you realize.

1. Your combo box doesn't have the correct number of columns defined. You have two columns in your underlying query, but the column count property of the combo box is set at 1, when it should be 2.

2. Your submit form shouldn't be bound to the tblMiner table. The subform isn't required, you should just use it, if you add the combo box to select the miner.

3. I am not sure that your structure is going to be efficient (or normalized). What if there are other minerals to be added? You should be able to add them easily without structure modifications to your database. I think your design is not normalized.

Unfortunately I have to go. I don't have time to assist right now with how you should go about this. Hopefully someone else might be able to help.
 
Your database has a few more issues than you realize.

1. Your combo box doesn't have the correct number of columns defined. You have two columns in your underlying query, but the column count property of the combo box is set at 1, when it should be 2.

2. Your submit form shouldn't be bound to the tblMiner table. The subform isn't required, you should just use it, if you add the combo box to select the miner.

3. I am not sure that your structure is going to be efficient (or normalized). What if there are other minerals to be added? You should be able to add them easily without structure modifications to your database. I think your design is not normalized.

Unfortunately I have to go. I don't have time to assist right now with how you should go about this. Hopefully someone else might be able to help.


1. I set the combobox that way on purpose so that it wouldn't also display the MinerID field. I just have those for internal data sorting/display purposes, it's all going to revolve around the actual name.

2. On this, I'm a little confused. Shouldn't the subform that contains the tbl_collection data be bound to that table? tbl_minerID shouldn't be bound to that table or subform, since the code sorts out the data to be displayed.

3. Fortunately, this isn't an issue that I have to worry about. All mineral names are static and don't require needing to be added to in the future; I included all of them in the DB.

Really appreciate the help/tips that you guys are providing :) Thanks so much.
 
1. I set the combobox that way on purpose so that it wouldn't also display the MinerID field. I just have those for internal data sorting/display purposes, it's all going to revolve around the actual name.
It's fine to set the ColumnWidth to 0" so it won't show, but if you don't set the number of columns to 2, the name field won't show when you use it.
2. On this, I'm a little confused. Shouldn't the subform that contains the tbl_collection data be bound to that table? tbl_minerID shouldn't be bound to that table or subform, since the code sorts out the data to be displayed.
You are currently having a main form that doesn't do anything. The subform isn't needed as you can do everything on the main form. So, move the combo box to the subform, get rid of the main form and just use the form that is in the subform.
3. Fortunately, this isn't an issue that I have to worry about. All mineral names are static and don't require needing to be added to in the future; I included all of them in the DB.
Just so you are aware about it and realize that it really shouldn't be in there like that. But, life is all about compromise and sometimes you might compromise for simplicity's sake. Just be aware that it COULD come back to bite you at any point when you go to try to pull meaningful data out.
 

Users who are viewing this thread

Back
Top Bottom