Trouble using the Dlookup function as an after_update

[Jon Smart]

Hi,


I'm new to the forum so if I have posted this question in the wrong section then I apologise.
I've just started building a database which contains data for a fish tagging project which allows us to enter data for when a fish was caught and subsequently when it is recaptured. I have a separate subform for both initial capture data and recapture data that link back to a main form (Using master and child links) that includes non-biological fields such as date, location, etc. My capture data subform is called catch_subfrm and links back to a table called catch_tbl. My recapture form and table are similarly named recapture_subfrm and recapture_tbl.

At the moment I enter data in these fields for both forms:

Tag_ID, Species_ID, Sex

In the catch_subform the first two are text boxes with a text format and sex is a combobox.

In my recapture_subform I have Tag_ID set up as a combobox which pulls the tag numbers from the catch_tbl and works fine. However, I would like to auto fill the next 2 columns automatically. I've attempted adding an after_update for the Tag_ID field in recapture_subform using the following code:

[Species_ID] = DLookup("[Species_ID]", "[Catch_tbl]", "[Tag_ID]=" & "[Tag_ID]")

However, it doesn't work and I get no error messages. I've used this code (with different fields) in different forms and tables without problems in the past but this one won't work for some reason. I've tried a few different suggestions for similar problems on other forums but with no success. If anyone could offer any advice I'd be very grateful. I'm hoping it's something simple which I've overlooked.

Cheers,
Jon

[June7]

Don't put variables in quote marks. Reference to field/control on form is a variable. Assuming Tag_ID is number field:

[Species_ID] = DLookup("[Species_ID]", "[Catch_tbl]", "[Tag_ID]=" & [Tag_ID])

[Jon Smart]

Don't put variables in quote marks. Reference to field/control on form is a variable. Assuming Tag_ID is number field:

[Species_ID] = DLookup("[Species_ID]", "[Catch_tbl]", "[Tag_ID]=" & [Tag_ID])

Thanks June7. I tried the above code as you suggested and got a Run-time error. The code was '3464' and it said "Data type mismatch in criteria expression". Any chance you could shed some light on what that means? I've seen it before in other databases I've worked with so it would be very useful to have an understanding of how to fix it. Also to answer you last question; Tag_ID is a text field as most of our tag numbers start with a letter to identify the type of tag (e.g. R7000). Will this affect the code I need to use?

Thanks for your help, it's been very appreciated.

Jon

[June7]

Text field requires apostrophe delimiters around the text criteria.

[Species_ID] = DLookup("[Species_ID]", "[Catch_tbl]", "[Tag_ID]='" & [Tag_ID] & "'")

Dates would use # delimiter.

[Jon Smart]

Thanks June7. Unfortunately that still hasn't worked. There's no error message at all this time. Is there a mistake I could have made in my table design that would prevent this from working? I've successfully used this code with other tables so I'm now wondering if there's an issue with either my catch_tbl or recapture_tbl. Should these be linked by the Tag_ID field as a relationship?

Cheers,

[June7]

I was only correcting your DLookup syntax. No idea if issue with data structure. Don't know enough about it.
If you want to provide db for analysis, follow instructions at bottom of my post.

[Jon Smart]

Thanks June7. I've tried compressing my db but it won't compress down below 2mb. I'll keep trying on my own for the time being and hopefully I'll figure it out eventually.

Thanks,

[June7]

You removed records and ran Compact & Repair? Zip file still over 2mb?

[Jon Smart]

Ah, I missed that step. Ok now the file is more manageable. I've left a couple of record in the forms so you get an idea for the information I'm trying to work with. It's all fictional though as I've yet to start entering data into it. I've already used the Dlookup function in the catch_subfrm so I'm a little puzzled as to why it won't work in the recapture_subfrm. If you could shed any light on this I would be very grateful.

Cheers!

[June7]

Not sure I would structure data this way.

A capture is a capture regardless if it is the 1st or the 100th. Why not just one table for all capture records instead of two capture tables?

The Relationships builder shows Catch_tbl related to Recaptures_tbl related back to the Catch_tbl. I don't see how this can be workable. Tables cannot be both parent and child to the same related table. Never seen this before. Consider:

Species_list_tbl

Tags_list_tbl
TagID (primary key)
SpeciesID
other info about the tagged individual

Trip_tbl

CatchHistory_tbl
CatchID (primary key)
TripID (foreign key)
TagID (foreign key)
other info about actions performed and observations during that catch

[Jon Smart]

Thanks. I'll give those suggestions some thought. The data is structured this way as I've based the db on the structure of an existing much larger database that we're working from. The purpose of this db is for me to have my own records of animals that are pertinent to my work with fields that aren't available in the other db. However, I want the option to merge the data later on in case this is useful for other researchers. I'm not sure why 2 catch_tbl relationships are showing up in the relationships window. I keep deleting the duplicate but it reappears immediately. Is there anything in my current structure which would give me difficulty in using the dlookup function?

Cheers.

[June7]

I've never seen table links that did not involve a PK field. That these tables are linking on two fields, neither of which is a pk, must have something to do with causing the double join. As I said, never seen this before.


I didn't see any textboxes with the DLookup expression so I created them in Detail section of subforms and pasted the expression into ControlSource. Works fine on the Tags tab. Returns the SpeciesID. For Tag_ID the SpeciesID is 70. Get nothing on the Recaptures tab because:

The value of the Tag_ID combobox is Catch_ID, not Tag_ID. Why is Catch_ID in the combobox RowSource? The BoundColumn is set to column 1 which is the Catch_ID. Do you want to save Catch_ID or Tag_ID.

Expression for the Recaptures tab would be:
=DLookUp("[Species_ID]","[Catch_tbl]","[Catch_ID]=" & [Tag_ID])
or because column index begins with 0, the Tag_ID is in column 2 so index is 1
=DLookup("[Species_ID]","[Catch_tbl]","[Tag_ID]=" & [Tag_ID].[Column](1))


Why is Tag_ID in Catch_tbl a text type and in Recaptures_tbl is number type?

[Jon Smart]

Thanks June7. I'm also becoming aware of the problems you mentioned so given that I've only just started designing this database I think I'll spend an hour or two and start over to correct the structure. I'll make sure I consider all your advice when doing as it will be very helpful. Then if I still have any issues I'll let you know. Thanks again for all your help. It's nice to know there are people out there who can be this helpful.

Cheers,
Jon

[Jon Smart]

I've changed the tables so that all the relationships are joined through primary keys. All the codes work great now. Thanks for your help!