chopping up one field to populate three

[aphelps]

Hi, again--

I have a primary key, NestCode. NestCode is made up of two letters (which represent the island code on which the nest occurs), then three numbers (which represent the number of that nest on that island), then two numbers (which is the year code). For instance, LB00102 is the first nest on Lower Bernard I. in 2002.

Now, below the nest code field on the form are three more fields, IslandCode, NestNumber, and YearCode. What I want to do is have these three autopopulate based on the entry in the NestCode field. I have tried to do this with default values of:

=Left([NestCode], 2)
=Mid([NestCode], 3, 3)
=Right([NestCode], 2)

I've also tried it with the table referenced as well, though I'm not sure the syntax is right (ie, "=Left([tblNestLocation:NestCode], 2)").

However, none of these provide me with any values (or any sort of error, just leaves the fields blank, I can type in the values but they don't fill in automatically).

This all occurs in Access2K on Win2K. The form in which this all happens is designed for data entry across multiple tables and is based on a multi-table query.

Any ideas for someone that 1-does not use VBA and 2-understands that the scenario described herein involves storing redundant data (I have reasons for needing NestCode split and saved separately)?

Thanks--Adam

 

[Fizzio]

The reason you are getting blank is that as soon as you are getting to a new record, the form is trying to split a current non-existent primary key into its fundamentals. To effectively achieve this requires the use of VBA I'm afraid - good place to start to learn!

On the After_Update of the NestCode control on the form
ie
Private Sub NestCode_AfterUpdate()
me.NameofControl=Left(me.nestcode, 2)
me.NameofControl=mid(me.nestcode, 3,3)
me.NameofControl=right(me.nestcode, 2)
end sub

Set the controlsource of the respective controls to their corresponding fields do that the data is saved. Do you Really need to store this though??

 

[aphelps]

I get an error message when I put the code into the zoom box in "After Update," something about it can't find the macro. However, I'm going to put this whole process on hold for a bit while I sort out the queries I am going to want (or rather, the queries that the user is going to want). If I can sort out how to build the queries using the left(), right(), and mid() functions, then this splitting process will not be necessary. However, it'll be a few hours till I get to that point. Thanks for the help, and I may be back soon (by which time you'll most likely be on your way home, but that's my problem!).

Thanks for your help--Adam

 

[Fizzio]

2 quick points
1) Select [event procedure] in the After Update event and then press the ... button - the code goes there.
2) the query option is spot on. To get the relevant data for example, set one field as the fill NestCode, the next field as (you will need to type this) Front:=Left([NestCode],2), the next field could be Middle:=Mid([NestCode],3,3]), etc.

Hope this helps you along

 

[aphelps]

I haven't given it much thought yet, but the only problem I foresee is that I'll want the full name of the island to appear in the query results. For instance, I know how to split the first two letters off to find all nests on an island. However, I need to figure out if I can use these two letters that I split in my island code / island name lookup table. That is, I might be able to see in my query that there were 23 successful American black duck nests on UB, but can I see that there were that many on Upper Bernard? Realistically it probably matters little, but that's how I would prefer to have the query report records.

I'll get to that yet this afternoon, and keep you posted.

Adam

 

[Fizzio]

If you have a lookup table, this is relatively simple. Your lookup table should basically just be

tblIslands
IslandID
IslandCode
IslandName

Example
1
UB
Upper Bernard.

You would then need 2 queries, one to split the primay key then a second (based on the first query) to pull off the relevant data via joins you would create in the query design window. This is a little more advanced query design but it will be worth tinkering with.
What is interesting is that your processes are the reverse to usual (I think) normally, you input data for example data for Island, Nest Number and Date then dynamically create the ID, not the other way around! - Post back if you need any more help.

 

[aphelps]

Thanks--the tip on making it two queries saved me a headache. I've now created a large list of parameter queries that allows the user to query by any one or any combination of island (based on the code), year (based on the code), and species. Now I'd like to try to figure out how to put dropdown lists from the lookup into the parameter request box instead of letting the user type in the island code...

This DB is being developed for a waterfowl biologist with whom I work who has one year of data already, which is why the procedure seems backward. It's all on cards now, hundreds of nests, and it seemed more reasonable to fit his system into the new DB than to try to get him to change his methods to fit into easier DB development. Besides, if I had fields for island code, nest #, and year, a dynamically-generated NestCode would be storing redundant data, too, just as much as splitting the NestCode into three additional fields would be.

In the lookup table you suggested in the last post, you listed an island ID, then code, then name. I've made the codes unique, so my lookup tables typically only have two fields (the related code and the full text of whatever they're translating). Is there some benefit that I am not seeing to include an autonumber ID, if the codes are supposed to be unique anyway? Seems like storing more data than necessary.

Thanks again for your help, and I'll let you know how it comes along.

Adam

 

[Fizzio]

The reason for using an autonumber in the lookup field is that in the main table, you would only store the number associated with that record. It is much more efficient and creates less headaches if you link via the autonumber rather than text.
The only way to use dropdown boxes is to use Query By Form. Create a form with unbound controls populated with the values you want to query on. you then use the reference to the form control in the query ie Forms!QueryForm!IslandCode

You are right about the primary ID and duplication of info. You do not actually store this key, just create it dynamically so that you can have a 'record number' of sorts.