Text Display 1

[ProtegeV2]

In an Access form, what is the easiest way to link a field that only displays a description (text) of the number that is punched in? that ALSO allows data entry of numbers that may not match what is in the description table.

[Project Request Table].[Network number] is the form field that allows data entry;
[tblCPANetNum].[NetworkDesc] is the matching text description;

If it's DLOOKUP can you explain. Thanks!

 

[RickSpr]

Is this what you mean?
You have a form based on table [Project Request Table] which users use to enter a number in a text box. Whenever they enter a value, you want to look up the matching description [NetworkDesc] in table [tblCPANetNum] and display it on the form. There may be no matching description in [tblCPANetNum].

If that's correct, you would basically use DLookup() in the AfterUpdate event procedure for [Network number], and assign it to a locked text box on the form.

For example, with the following assumptions:
- The form field into which the number is entered is [txtNetNum]
- The description is to be displayed in text box [txtNetDesc]
- tblCPANetNum has a unique column [NetNum]
The code would be:
[txtNetDesc] = DLookup("[NetworkDesc]", "[tblCPANetNum]", _
"[NetNum]=" & [txtNetNum])

Note that if no match is found in [tblCPANetNum], DLookup returns Null. When Null is assigned to [txtNetDesc], it is displayed as an empty text box.

If the form allows editing (you only mentioned data entry), you would also want to use this statement in the Form_Current event procedure.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[willir]

Hmmmm

displays a description ... that ALSO allows data entry of numbers

In my books, this is not easily done.

The text box is bound to the NetworkNumber in the table.

To display the description for the NetworkNumber is easily done using a ComboBox.

ControlSource or Bound field: NetworkNumber
RowSource: Select NetworkNumber, NetworkDesc ...
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";1.5"
ListWidth: 1.5"

The key here is the 0" for the first column which is determined by the SELECT statement - NetworkNumber. The 0" hides the column and the end user sees the next column over, NetworkDesc.

...Moving on
Data entry. In the above example, Access will search by the first visible column - specifically NetworkDesc even though it binds or stores the NetworkNumber. This tends to be a good thing - often the end user will work with names rather than keys.

I have not tried this, and I have other priorties at present, but perhaps a work-around for what you want to do is to Change ColumnWidths.

When doing data entry...

Dim strQ as String

strQ = Chr$(34)

Me.YourComboBox.ColumnWidths = "1" & strQ & ";1.5" & strQ
' to represent 1";1.5"
' now the end user should be able to enter a number

When displaying / current record toggle the value for the first column to hide it...


Dim strQ as String

strQ = Chr$(34)

Me.YourComboBox.ColumnWidths = "0" & strQ & ";1.5" & strQ
' to represent 0";1.5"
' now the end will see the description


Just a thought...
Richard

 

[ProtegeV2]

Hi RickSpr,

I'll try yours first. Can you tell me what is causing a compilation error:

Private Sub Network_Number_AfterUpdate()

[txtNetDesc] = DLookup("NetworkDesc]", "[tblCPANetNum]",
"[NetNum]=" & [txtNetNum])

End Sub

I named a text box "TxtNetDesc" and the only property I changed is Locked "Yes". The description does not display (yet) because the code is missing something.

Thank you.

 

[RickSpr]

What is the error message or number? Are you sure it's a compilation error and not a runtime error?

I notice you are missing a "[" there, but that would cause a runtime error, not a compile error.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[ProtegeV2]

I added the [ and I received "Compilation Error - Expected: expression".

 

[ProtegeV2]

I think the code is OK now, I also forgot the _ at the end of the first line. Nothing is appearing in the text box though. Is it supposed to be unbound?

 

[ProtegeV2]

Anyone?! Can you tell me how to get this to work? thanks!!

 

[RickSpr]

Yes, the [txtNetDesc] control should be unbound.

Create a query object using this SQL statement, which is identical to that generated by the DLookup call:
SELECT [NetworkDesc] FROM [tblCPANetNum] WHERE
[NetNum]=[Enter network number]
Run the query. It will prompt you for a network number, and its result should be the value displayed in the text box. Run it several times, using network numbers that are both found and not found in tblCPANetNum.

Are the results always blank?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[ProtegeV2]

Yes, the query works - it gives the corresponding description and blanks for unlisted.

What is [txtNetNum] used for? I receive run-time error '2465' ... can't find the field "txtNetNum" referred to in your expression.

 

[RickSpr]

Refer back to the assumptions in my first response:

- The form field into which the number is entered is [txtNetNum]

You didn't give any actual control names, so I had to invent a name. You should have changed it to the name of the actual control you are using. (This is why the FAQ mentioned in my signature recommends giving the actual names of Access objects. Please take a look at the FAQ.)

Note: I started out assuming you knew you couldn't bind a text box control to two different fields, nor could you bind it to one field (network number) and display a different field (description) in it. Richard (willir) was alluding to this fact as well. You aren't trying to have it work where you type in a number and it gets changed to a description, are you?


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[ProtegeV2]

Thanks Rick! The form has a field to enter the network number. I want the description to appear in a separate field (no edits allowed) below the number. If there is not match, I would like the description to be blank (no error messages).

If you refer back to my original message I gave the form field name:

[Project Request Table].[Network Number] is the form field that allows data entry

I changed the name to [Network Number] in After Update and entered a number. The description appears, however now the same description also appears now on all the following records.

 

[ProtegeV2]

Also, where do I find the Form_Current event procedure?

 

[RickSpr]

If you refer back to my original message I gave the form field name

So you did. My apologies. I was confused by you calling it a field (it's actually a control; fields are things in tables and recordsets) and by it being in a form named [Project Request Table]. I thought it was a field in a table.

The description doesn't change as you change records because you haven't created the Form_Current event procedure yet. (BTW, I'm assuming [Network Number] is bound to a field in the record source.) There are several ways to create it. The way I usually do it is to open the form in Design View, click the form selector (the little blank rectangle in the upper left corner), and in the Properties sheet click the "..." button for the On Current property. This will open the module with a skeleton Form_Current procedure. Just paste in the same code that you put in the [Network Number]_AfterUpdate procedure.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[ProtegeV2]

Just so we're on the same page [Network Number] is a field in the table [Project Request Table] that is used in this form for data entry. When the number is entered, the matching description will display in [txtNetDesc].

I pasted the same code in After_Update in the form's On Current:

Private Sub Form_Current()

[txtNetDesc] = DLookup("[NetworkDesc]", "[tblCPANetNum]", _
"[NetNum]=" & [Network Number])

End Sub

...and get Run-time error '3075' Syntax error (missing operator) in query expression '[NetNum]='.

when I move to the next record.

 

[RickSpr]

You said that this code works in the AfterUpdate event. If you cut and pasted it from there to the Current event procedure, I don't see how it can not be working.

Is there a field called '[NetNum]' in table [tblCPANetNum]? If not, substitute the name of the network number field in the table. Again, I had to invent some names in my assumptions. You are supposed to substitute the actual names where necessary.

Also, please verify that "Network Number" is actually a number and not an alphanumeric code.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[ProtegeV2]

I copied the After_Update code and pasted it into OnCurrent so it is exactly the same!

There are two fields in tblCPANetNum: [NetNum]has a number (double integer) data type and [NetworkDesc] is the text description.

Network Number is alphanumeric (I didn't set that field up!). Some users also type in the cost centre abbreviation or instructions on where to send the invoice in this field.

Is that the problem? does it have to be a number data type?

Thanks.

 

[RickSpr]

double integer" - I guess you mean long integer?

"Network Number is alphanumeric" - ok, that would explain the 3075 error. Alphanumeric (Text) fields require a slightly different syntax for the DLookup(). (Be sure to cut and paste this, because the adjacent apostrophes and quotes are easily mistaken for one another.)
[txtNetDesc] = DLookup "[NetworkDesc]", "[tblCPANetNum]", _
"[NetNum]='" & [Network Number] & "'")
The difference is that for a text field, the constant value you are looking up has to be surrounded by apostrophes (or quotes, but apostrophes are easier to code in VBA).

If you didn't get the error in the AfterUpdate event, it's because you typed in only digits. You should have gotten the same error if you typed in something that wasn't numeric.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[ProtegeV2]

Thanks so much, it's working now!!!