DAMN Access Newbie needs some help !

[Schorsch]

Hi,

i created a combo box, but when I want to make a new entry, the following message appears:

Field cannot be updated. (Error 3164)

whats wrong ?

thx

 

[mixedguy]

Try going to the Property window of the combo box. Click on the Data Tab and look down at the property called "Limit to List." If the "Limit to List" property is set to "Yes" then you will not be able to add any new entries to your combo box. You will need to change this property to "No." Then you'll be able to pick from the list or type in your own.

I hope this helps.

 

[DBL]

I would have thought that if you were trying to add a new entry to a combo box with the Limit to List set to Yes you would get the "This item isn't on the list" error rather than the one you are getting.

Make sure the combo box is bound to a field on your form. If you open the properties for the combo box and look at the "control source" it should have the name of the field that the combo box is bound to there.

 

[Schorsch]

there is no property "Limit to List", only the property "Locked", this is already set on "NO".

I think there is something wrong with my tables plz help

regards,
Schorsch

 

[DBL]

Open the form in design view, right click the combo box, select Properties and then the Data tab, you'll see the Limit to List property there. Remeber that if you set the limit to list to No then everybody can add anything they like to the list if they don't find a match.

 

[Schorsch]

can somebody check my form ? I simply don't check this stuff

I've stuck the mdb file to the topic.

Thanks in advance!!

regards,
Schorsch

 

[DBL]

Having had a very quick look, there are problems with the structure of your tables, queries and the form. Without understanding what you are trying to do with the form and how the main form data is linked to the subform data I can't really advise you. Maybe you could give us a bit more info.

When you say "I simply don't check this stuff" do you mean won't, don't or can't!

 

[Schorsch]

I'm an Access Newbie, doing this since 2 days, but I have to create that database for my boss until next week.

did you still look into the relationships ?

and what is wrong with the structure of the tables ?

regards,
Schorsch

 

[DBL]

I've had a look at the relationships and it's making a little more sense. I've fiddled with the form and subform and set up the structure of each properly. I've also changed the control source for the combo box. The combo box should be looking to the ReviewedBy table and the ID number should be saved to the ReviewedID field in the ReviewVesselView join table. Your structure is actually not far off and you've done well to get the join tables, that's something that a lot of people miss first off.

Some tips. I would add an autonumber field to all your tables and make that your primary key field - it makes things a lot easier. If you want to create your own number reference you can do that too but an autonumber field makes sure that you can't create duplicate records - I find it's more stable than a self generated number as a primary key.

Don't have spaces in your table, field, query, form names. If you have to put a space, use an Underscore, but Access does prefer it if you don't use spaces and it makes referencing fields a lot easier. It's also handy to prefix your different object types - tblReviewedBy; qryReviewedBy; frmReviewedBy. Again it makes referencing your objects easier.

A personal favourite of mine is not using the same field name in different tables. If you create a query that needs both the AuthorVesselView and AuthorName you have AuthorID in both tables. It can be difficult to decipher which AuthorID field from which table you have, especially if you need to reference that field in VBA. I like to prefix table fields with something from the table name to make the field name unique - AVVAuthorID ANAuthorID

Shame on your boss for putting you under this pressure!

 

[Adrianna]

Schorsch,
I'm only at work for 1 more hour, but I will be glad to assist you as well. I would admit that you've provided us with ver little to go on. What exactly are you trying to do. Be very specific:

What form are you having trouble with?

What combo box are you getting the error on?

Why do you have so many tables? If the lists are going to be very dynamic, that's great, but if some of them are static, I would use the "value list" function inside of the form to feed a single field.

If you can clearly explain what you're trying to do, you will be more likely to get answers!!

 

[Schorsch]

@DBL:

thanks for help but it doesn't work.

when i go through the records the value in the combo box is always the same, also it doesn't save the values.

regards,
Schorsch

P.S.: sorry that I'm so rare with words but it's hard to articulate for me...

 

[DBL]

Open up your table ReviewTeam and the first row has an number of 0 - remove that line, it's happened while you were having the problems and it's causing the problems. When you open the form now you'll see in the subform a blank row where you can add the next reviewer. The records are being saved to the table ReviewVesselView which is the join field between VesselView and Review Team. It lists the document ID numbers for VesselView and the ID numbers of the people doing the reviewing. If you have four people doing the reviewing, you'll have four identical DocumentID's with the ID numbers of each of the four reviewers beside them.

Hope that makes sense

 

[Adrianna]

Just explain what you want

Can you at least explain what you want the database to do. FOr someone who has only been working with access for two days, you seems to have done a lot.

I'd like to help you, but if you can't express what you need to accomplish then it's going to be almost impossible to help you.

Your form just doesn't make a lot of sense to me. Seems like you need a major overhaul on structure.

Why are you trying to enter information into your combo box from the form that you're viewing. The combobox in your form is gathering data FROM a table/query, not for entering data into it. if you want to enter additional fields into your combobox then create a bound text box to the table, then allow users to select from the combobox!

I would offer help VIA realtime, but if you can't explain yourself well enough then realtime won't help either.

 

[DBL]

Hi Adrianna

I think that what Schorsch was trying to do was just select an item from the combo box rather than adding a new option to it. Because the structure of the form was wrong it wouldn't even let him select an option from the combo to add to the join table.

 

[Schorsch]

@Adrianna

that's just a test form, not the final form. I just wanted to test how the whole thing works.

so what I want (resp. my boss):

you should choose a couple of Reviewers from the combo box, ALSO you must be able to add new names to the combo box (put data in)

so how can I realize that ? remember I'm a bloody Access Newbie

thanks

edit: @DBL: it should be possible to put data in, what do I have to change within the tables ?

 

[David R]

Look up the NotInList property

You can use that to open another form which allows you to "add new names" to the list. Doing it on the fly is somewhat complicated, but possible. Try this thread: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q197526

 

[Schorsch]

oooh is there no easier method to realize that ?

regards,

Schorsch

 

[Adrianna]

Okay....it's really simple.

Create a form with a bound textbox, control source [YourComboField]
Add a button to your current form, taking you to the form you just created. The form allows you to make additions to the combo box and the combobox on your MAINform will allow you to select the reviewer from a list, leading back to your maintable with will store the records.

Later you will find that it is much easier to restrict access to who has permissions to add reviewers!

I would atually suggest consolidating a bunch of your tables...or else you are going to force yourself into way to many unnecissary queries later...not to mention the difficulty of maintaining the structure.

If I have time later this morning. I will throw together a little example for you!
If I had access to MSN or AIM, I'd find it more productive to teach you and explain, but i think PM's in the forum are the closest that we will get to that.

 

[Franky G]

I also wanted to use a combo box on a form and be able to type directly into the combo box, and add this new value to the underlying table. This worked good for me;

Private Sub Event_ Yourfieldname_NotInList(NewData As String, Response As Integer)
Dim rec As Recordset

If MsgBox("Site is not in list. Ok to add it now?", vbOKCancel, "Adding Data") = vbOK Then
Set rec = CurrentDb.OpenRecordset("YourTableName")
rec.AddNew
rec!Yourfieldname = NewData
rec.Update
Set rec = Nothing
Response = acDataErrAdded
Else
Response = acDataErrContinue
Event_Yourfieldname.Undo
End If
End Sub

Hope thats of some help...

Franky G

 

[ColinEssex]

as an append to Franky's answer.

Don't forget to set the LimitToList property of the combo to 'True'
you need to trap the fact that it's not in the list.

Col