Please check . . .

[tbaxter]

http://www.access-programmers.co.uk/ubb/Forum4/HTML/005557.html

It's down the page a way (re: Lookup)- I got some help from Jack but am having trouble with the results!

Sorry about the "redirect" post but I need to get this done and I'm SO very close!

 

[Jack Cowley]

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

The "Location Form" needs to be replaced with the Query OR the Table that has the Location Field. If your form "Location Form" is based on a Query then put in the query name; if it is based on a Table then put in the Table name. The code above is trying to verify that the Location data was indeed added.....

 

[tbaxter]

Hi Jack,

I thought of that so I changed the name of the form and the query to just "Location."

Still no luck.

I'll have to "play" with it some more tomorrow.

Thanks!

 

[tbaxter]

Well, yes, it does! But I was desperate and had to try *something*!

However, that didn't work either so I'm probably just going to start over. I'll rename the form Location Form, the query Location Query, and the table Location Table, then edit the code and try again. Maybe I just need to comb the code word-by-word and make sure I didn't mis-spell something.

 

[tbaxter]

So far no luck. It's working great until I close the Location Form - then I get the "Please try again!" message. But if I escape out, close the main form, open it again, and re-enter the same information, the location is there!

Is there any other info I could give that could help someone figure out where I'm going wrong? I certainly don't want to turn over a database that has "bugs" even if it works in a round-about sort of fashion . . .

Just wanted to mention one more thing - this my first-ever attempt to write code - and all I did was take the example in the article, copy it, and just change some names and text.

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

 

[tbaxter]

It's still telling me "Please try again!" yet when I escape out and close the main form, then reopen and start inputting again, it's now available as a location in the combo box.

I've been working on this for 2 days straight now. I'm getting really frustrated because I can't figure out where the error is. It works, but it doesn't work without extra effort on the user's part - which wouldn't necessarily be a problem if my end user was the only one to use it. But what about the person who might move into the job after her . . . or the other departments she might pass the dBase on to for them to use?

I definitely appreciate the help I've received here (and think I've managed to learn a thing or two!) - but I'll be super ecstatic if someone can help me "debug" this problem!

 

[tbaxter]

I am hoping that someone will be able to take a look a the overall picture and see where my problem is with the NotInList business. So, I'm going to give the basic dBase structure here plus the code for NotInList.

TABLES:

Name Table
NameID (Primary Key, autonumber)
LastName (text)

Location Table
LocationID (Primary Key, autonumber)
Location (text)

Clearance Table
ClearID (Primary Key, autonumber)
DateGen (date/time)
DateExp (date/time)
NameID (foreign key, number - long integer)
LocationID (foreign key, Lookup, number - long integer)

QUERIES:

Name Query (based on Name Table)

Location Query (based on Location Table)

Clearance (based on Clearance Table)

Complete Query (based on all 3 tables) - NameID (Name Query), LastName (Name Query), ClearID (Clearance Query), DateGen (Clearance Query), DateExp (Clearance Query), LocationID (Clearance Query)

FORMS:
Note: these 1st two were generated via FormWizard basing on Complete Query

Personnel Clearance Form (NameID and LastName)

Clearance Detail Subform (ClearID, DateGen, DateExp, LocationID)

Location Form (based on Location Query with LocationID, Location)

Okay, here's the code:

Private Sub Location_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String

CR = Chr$(13)

' Exit this subroutine if the combo box was cleared.
If NewData = " " Then Exit Sub

' Ask the user if he or she wishes to add the new customer.
Msg = " ' " & NewData & " ' is not in the list." & CR & CR
Msg = Msg & "Do you wish to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the Location Form in data entry
' mode as a dialog form, passing the new location in NewData
' to the OpenForm method's OpenArgs argument. The OpenArgs
' argument is used in Location Form's Form_Load event procedure.
DoCmd.OpenForm "Location Form", , , , acFormAdd, acDialog, NewData
End If

' Look for the location the user created in the Location Form.
Result = DLookup("[Location]", "Location Query", "[Location]=' " & NewData & " ' ")
If IsNull(Result) Then
' If the location was not created, set the Response agrument
' to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again!"
Else
' If the location was created, set the Response argument to
' indicate that the new data is being added.
Response = acDataErrAdded
End If

End Sub

Does anyone see my problem anywhere? To reiterate, if the location is not in the list, the MsgBox opens and asks if I want to add. I click yes, it opens Location Form. Location is there, along with the autonum LocationID, I click Save then Close, which is when I get the "Please try again!" MsgBox. Yet if I escape out of the Clearance Detail SubForm, close the Personnel Clearance Form, then reopen and add the same location, it's there in the combo box!

Thanks for any and all help.


[This message has been edited by tbaxter (edited 02-04-2002).]

[This message has been edited by tbaxter (edited 02-04-2002).]

 

[tbaxter]

Hi Pat,

I'm way out of my depth, here! I have the code on NotInList of the Location field in the Clearance Detail Subform.

In the OnLoad Event Procedure of the Location Form, I have:

Private Sub Form_Load ()
If Not IsNull(Me.OpenArgs) Then
' If form's OpenArgs property has a value, assign the contents
' of OpenArgs to the Location field. OpenArgs will contain
' a location if this form is opened using the OpenForm
' method with an OpenArgs argument, as done in the Clearance
' Detail Subform's Location_NotInList even procedure.
Me![Location] = Me.OpenArgs
End If
End Sub

Which code is in the wrong place and where does it actually belong?

Thanks for your help. I apologize if I seem to be dense about this but this is my first ever attempt to "write" code (actually, I pretty much just copied it from Q197526.

Tina

P.S. the same code that shows up in the NotInList event procedure also shows up at BeforeUpdate on the Location field of the subform (although I didn't put it there and have no idea how it got there!).

[This message has been edited by tbaxter (edited 02-04-2002).]

[This message has been edited by tbaxter (edited 02-04-2002).]

 

[tbaxter]

Just bumping this up, hoping someone will notice it . . .

 

[tbaxter]

 

[tbaxter]

Pat? Jack? Anyone?

I don't mean to be a pest but is there anyone out there who is experienced at programming in Access that could possibly take a look at my database and let me know where the problem is?

I'm SO close and this issue is the only thing holding me up!

I will be ever grateful to whomever is able to help me fix this problem!

 

[tbaxter]

Hi Pat,

Thanks for responding!

I'm not sure how you mean. The problem is that the location IS being added . . . but doesn't show up unless I escape out of the new record (thus not saving anything), close the main form, then re-open. Yet if I check the location table, it's there! Somehow, it's just not getting to the combo box in the form unless I close it all out and then re-open the main form.

This is something that seems like it should be so easy to do but it's certainly giving me fits.

Do I need to include an update query in there somewhere so that when the new location and location i.d. are inputted into the location table that they are then added to the combo box? But then, with the combo box based on the complete query (of which the location table is a part), shouldn't that be automatic?

I started looking at Option 1 in the article (using code to add a record to a table) but it gets into inputting the primary key, which is not necessary with mine since it's an autonumber field.

Tina

 

[Rich_Lovina]

I had similar problems with DLookup at first...can take a couple of weeks to get it right. Jack, Pat, etc are great help.

In programming there are always many "Roads to Rome (roam) to achieve same thing.

Have a look at my adjacent post RI still beating me...it may offer some help.
Hv you searched all the Forum's Dlookup postings?
That can help too.
Only suggestions from above:

1. Try and keep combonames, field & table names slightly different in all coding eg. Using Location, I have a LOC_INPUT, LOCTABLE, LOC_ID or LOC_CODE, etc; so you dont confuse yrself.

2. In one of Jack's codes we found that ' " on different keyboards was a problem, and was ONLY fixed when I back-spaced out an ' and typed it in again.

Good luck, you'll get there.

 

[tbaxter]

Thank you, Pat and Rich! I will try that later today - will have to work on it at home since my youngest was sick all night - just dropped in to work to pick a few things up. I'll report back as soon as I can.

Tina

 

[tbaxter]

Hi Pat,

I put the following in the AfterInsert event procedure of the Location Form (which adds the new location):

Private Sub Form_AfterInsert()
' Requery the table to recreate the recordset in the Clearance
' Detail Subform combobox
Forms!Clearance Detail Subform!LocationID.Requery
End Sub

Still no worky - I get a syntax error and the Forms!Clearance Detail Subform!LocationID.Requery shows up in red.

I think I'm just a hopeless case. {sigh}

My problem is I don't even know enough to be dangerous! I really do want to learn how to use Access WELL but just don't have time to devote to doing just that. The class I took last year was more of an introductory class and I have no idea where to go locally to get more in-depth. Would it be worthwhile to take a Visual Basic programming class? I think that's where my weakness lies because, although I can enter the code recommended, I don't understand how (or why) it works (or doesn't work).

Thanks for all your help!

 

[tbaxter]

Hi Pat,

In an earlier response you said: "Your code is in the wrong event. If you want to stop an update, the correct place to put the code is in the BeforeUpdate event."

I got to thinking about this and still am not clear - I don't want to stop the event, I want it to work! The location is being added but the combo box of the form still doesn't recognize that yet.

"Depending on the situation, you may want to use the BeforeUpdate event of the control or the BeforeUpdate event of the form. In any case, if the edit fails, you need to specify two things:
Cancel = True
Me.SomeField.Undo"

My next question is: where in the code of NotInList or BeforeUpdate does the above go? And is SomeField the actual name of the field being updated? IOW, would mine read Me.Location.Undo?

I haven't had much time to play with this but hope to get it done this week. If I can't get it to work, I'll probably just tell my sister "Sorry but I tried!"

I still need to study Rick's response and see if I can get somewhere with his suggestions.

There just aren't enough hours in the day!