a reference i cant get to work

[BMan111]

Ok, so maybe one of you can help me with this, cus lord knows neither me or my boss can figure it out.

I'm trying to drop a 7 digit code from a query that I'm using to create a project number, into an input field in a form for another table all together. When I go to the input forms text box for the project number I have an imput mask set up as follows: "000\-00\-00";0;_.

The mask works fine though, and is not really the point of this post. The problem I AM having is that when I go to the default value property of that text box, it wont let me reference the query where I have my project number stored. It would not let me add it to the form because it was not related to the project table in anyway way. I have tried several different ways with which to reference it, and I keep getting the same thing in the text box: #Name? The main way the resoures I have tell me to reference is [table/query name]![field name], is this wrong?

If anyone can give me any hints as to what I'm doing wrong, I would greatly appreciate it.

Thanks ahead of time.

Brandon
'he-who-really-shouldnt-be-the-Access-guy'

[KnooKie]

Where is the query that you are trying to reference ?

Opened in code ?
Bound to a form/object ?
What type of query is it ? Append ? Update etc ?

[BMan111]

While those are all fablous questions, I feel I can answer them all with one phrase; I have no idea.

They weren't created with code, because the machine I'm on has had something happen to it and the VBA stuff doesnt work (and my stupid IT department wont fix it as they claim I dont need it).

All I can tell you, and its not much cus im VERY new to Access, is that I used a basic query that finds the highest existing project number, then starts a new field that raises the 3rd digit of it by one, then adds on the more static last part of the number, giving me a query with one record with the last existing number, and the number for the next record. I know that does not give you a lot to go on, but the fact of the matter is, I just dont know how to answer your question, so I'm hopeing my description will help

Brandon

[KnooKie]

ok i guess my question is how are you running this 'basic query' that gives you the next project number ?

[BMan111]

God, I feel like such a noob... wait a mineut, I am a noob.

Ok, again, im not sure I can answer you with out just telling you what I did, which I'm sure wont contain all the information you would need to help me, but here goes.

I went in and made a query with design view. I added in my project table, which is how I got access to my project numbers. The format of the project number is xxx-yy-zz where x is a sequential number, y is the month, and x is the year.

I added the project number field to my query, and set it up to find and display only the max number. Then I made a new field called Next_Project_Number that used the following equation to get its value: Max(Format((Left([Project_Number],3)+1),"000")) & (Format(Date(),"mmyy")). This had the required affect.

So, to try to more directly answer your question, and to cover my butt so you wont get annoyed trying to help me, I think I am running a search query (cus it searches for the max value of Project_Number).

Does that answer your question? Again, I'm so sorry that I cant answer your question more directly, I've been self teaching myself Access so I know none of the technical language.

Brandon

[BMan111]

Can anyone tell me how to reference the last record before the new record, or how to select a specific field from a specific record number? If I couldfigure that out, I think I can solve my problem.

Brandon