Data Entry Form

[Jacobingram]

The result I am looking for is this:


The user selects a prospect which is made up of Prospect Name, Country,
Company and Prospect Type. They can then use the subform to add records
into the database with the above information already there.


At the moment I have four combo boxes in the Prospect part of the form.
The user can use the combo boxes to select a prospect. The document
subform has the "data entry" property enabled so the user enters
document names and locations.


The problem is that rather than inserting each document under prospect
names which already exist, Access is creating a new propsect (with the
same name but different ID) and filing them under that.


How can I use the combo boxes to navigate to a particular prospect
record (an add prospect feature is not required), and have access put
the documents inserted in the subform into this?


Here is a screenshot of the form if it will help:
http://img35.exs.cx/img35/9764/eg1.jpg


Thanks
Jacob

 

[monkeytunes]

We need to see the underlying table structure in order to see why the form isn't working. If you can post a copy of your database, then folks on these boards can analyze it and give you some feedback. Try compacting your database using the "Compact & Repair" option under the Tools menu in Access (Tools ->Database Utilities->Compact & Repair) and then put the db in a zip file.

 

[Jacobingram]

OK Thanks

Here is the link - www.geocities.com/edd_23/db1.zip

It is about 330kb

Any help would be greatly appreciated...The form in question is named form/document. I am a complete novice so if there are any other glaring errors please feel free to highlight them!!

Cheers
Jacob

 

[monkeytunes]

I only had time to take a quick poke around, and I didn't get a chance to modify it and try it out (got deadlines of my own, brothuh) but it looks like Access is creating a new record every time BECAUSE you have the main form in "Data Entry" mode.

What it sounds like you want is the ability to quickly put in new records in the subform, right? What I'd do is make the Parent form normal, and turn the subform's Data Entry property to yes. Then I'd use a combo box or list box to simply navigate to a company in question on the parent form.

W/r/t your tables, none of your relationships are enforcing referential integrity. The point of a Relational Database is to have this sort of integrity, where data only has to be stored once, and can't be entered haphazardly. In your individual tables, I would turn off the LookupFields (they will cause problems later on if you upgrade to a different DB environment, and will break if you ever change the name of a field or whatnot), and instead make sure that all the numerical values in my "many" tables are present in my "primary" table. (For instance, you have some fields which have a "0" in the ProspectID field, while "0" isn't present in the Prospect table's ID field.) Search for Referential Integrity on these forums (and pay special attention to posts by Pat Hartman) for more info.

And finally, for ease of use in the future, it's easier to keep track of all your database objects (tables, queries, forms, etc.) and controls (labels, text boxes, etc.) if you name them using db conventions like "tbl", "qry" "frm" and so on. Do a search for Naming Conventions on this forum for the links. (If you do it now, you may break some relationships and hierarchies within your db, and it can get tedious because Access gives stupid names to control by default, but once you get in the practice of doing it, it makes keeping track of things much easier.)

Let us know how things work out for you!

 

[Jacobingram]

Then I'd use a combo box or list box to simply navigate to a company in question on the parent form.

This is what i am having trouble with. Simple, I know - but I am a beginner!!

When not in data entry mode, the form has the record navigation buttons at the bottom...If you use the combo boxes, it simply edits the record that is currently selected (ie record 1 of 90). I want to use the combo boxes and not the bottom navigation bar to select a record in the parent form.

 

[Jacobingram]

OK - Update

Managed to get it so that the "Prospect Name" combo box controls which prospect record is selected. When changing the prospect name, it also changes the other combo boxes in the form to match the information it has on that prospect - great!

Problem - Lets say you open the form and it is automatically on record 1. When you change the prospect name, the country and company information appears, and the related documents appear in the subform. Everything looks fine, but if you go back to the prospect table you can see that it has changed the prospect name of record 1 to the one you were trying to navigate to.

Here is the link for the updated database - http://www.geocities.com/edd_23/db1backup2170.zip


Also, if anyone can suggest how I can integrate my code for cascading lists into this I would much appreciated it. I would like to be able to select a country and/or company and have the ProspectName combo box only show records with this property (ie prospects in the UK). I have got working code for this - see first link.

Any help would be greatly appreciated!!
Thanks
Jacob

 

[monkeytunes]

Hey Jacob,

I'm assuming you set up your combo boxes early on using the Access wizards, right? So, when you plunk a combo box down, the wizard launches and asks the following:

"How do you want your combo box to get its values?

• I want the combo box to look up the values in a table or query.
• I will type in the values that I want.
• Find a record on my form based on the values I selected in my combo box.

"

When you want to navigate, you would use the third choice. When you want to edit/input data, you use the first choice.

Since you want to navigate, the combo box in question will be created with the 3rd choice, using a field (which you choose during the wizard) in your pre-existing recordset for this form (which, in turn, is built on an query or a table). The fix to your problem might be as simple as rebuilding the problematic combo box using this third choice, and then possibly recreating the other combo boxes using the first choice if need be. (Personally, I've never found a use for typing in my own values, i.e. the second wizard choice, as it's a pain to update across multiple forms, and I'd rather store such things in their own table, related to other tables.)

What might help is if you make a copy of the form (Access will let you cut & paste database objects like forms for easy duplication!), and rebuild the combo boxes on the copy the way I suggested and see if it works. If it does, then you can compare the combo box properties across both forms (the untouched original and the modified copy). Both should have "[Event Procedure]" listen in the After Update property.

If you place your cursor in the After Update property and then click the "..." button, it will open the VBA editor, where you can look at both sets of code side by side, and this allows you to see exactly what Access is doing when you use the combo box tool. It will also let you see why (hopefully) one combo box is working while the other isn't.