Help W/ a form using Lookup - adding new data to "Lookup"

[tbaxter]

The place my sister works for needs to track companies to which their people are "cleared" to go and how long they are "cleared" for that location. I've developed a simple database that allows her to either input or view all "clearances" for each of their people. However, if a new location comes along, she needs to be able to add it.

I have 3 main tables: Personnel, Location, Clearance

Personnel contains 2 fields: NameID, LastName (that's all she wants - hopefully, there are no duplicate last names! Can always add in 1st name later very easily, though)

Location contains 2 fields: LocID, Location

Clearance contains fields: ClearID, DateGen, DateExp, NameID, LocID

The main form (based on a query from the 3 tables) shows the name and a subform showing all clearances for that person. Example:

Doe
ClearID DateGen DateExp Location
1 1/25/2002 2/25/2002 Company X (SomeCity, State)
2 1/25/2002 2/25/2002 Company Y (AnyCity, State)

and so forth. Location is a drop-down box where she can either click on the arrow and scroll to find her location or start typing and hope she gets a match. However, if there is no match and she wants to add a new location what is the best way to do that? Here's what I was thinking about:

1) Set it up so that she can select or enter a new one. But I don't know how to do that! Keep in mind, I don't write code - I just use wizards and properties!

2) Set it up so that she has to click a button to open another form (call it Location Form) and enter it that way.

BTW, I did it via option 2 but the locations were not showing up unless I first close the Main Form, even though they were present in the location table while the main form was still open.

Any suggestions?

Thanks!

 

[Jack Cowley]

This article will show you how to use the Not In List event of a combo box. Just copy the code, modify the field names to be the same as your and you should be good to go. When she types in a name that is not in the combo box she will be told so and be given the option to add it. I would suggest that you consider the second method described in the article.... Good luck.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q197526

 

[tbaxter]

Thanks, Jack! I'll print out the article and give it a try.

 

[tbaxter]

Okay, I went through the process described in Option 2 of ACC2000: Use NotInList Event to Add a Record to Combo Box (Q197526).

It started out working fine by asking if I wanted to add the location but then I got an error when click "yes" that I want to add it.

"Run-time error'2448
You cannot assign a value to this object."

Upon debug, the following was highlighted:

Me![Location] = Me.OpenArgs

I can't figure out what I did wrong!

To give you some info on dbase structure, the Location table is set up as follows:

Location (text field)
LocationID (primary key, autonumber)

I also have a Personnel Table:

NameID (primary key, autonumber)
LastName (text field)

And a Clearance Table:

ClearID (primary key, autonumber)
DateGen (date/time field)
DateExp (date/time field)
LocationID (foreign key, number)
NameID (foreign key, number)

Any ideas?

 

[tbaxter]

Just trying to bump up the topic, hoping someone will notice it and be able to help!

 

[tbaxter]

Okay, I've gotten past the 1st run-time error. This is the one I'm getting now:

"Run-time error '3078': The Microsoft Jet database engine cannot find the input table or query 'Location Form'. Make sure it exists and that its name is spelled correctly."

When I click on debug, the following line is highlighted:

Result = DLookup("[Location]", "Location Form", "[Location] = ' " & NewData & " ' ")

Now, the form name IS "Location Form" and it's based on a query named "Location Query."

I'm so close but it's still eluding me! Please help!

Added: I had the same problems when I used option 2 in the article in the Northwind database.

Interesting note: It adds the data I input but makes it appear it doesn't - and tells me to "Try again!" Yet, if I close the main form, then open it, I can now select the new location because it now appears in the combo box.

This one problem is keeping me from getting this database off my "desk" and into the hands of the end user. Please help!


[This message has been edited by tbaxter (edited 01-31-2002).]