Simple one (for you guys!)

[Jonathanw]

Hi,

I'm struggling with a fairly simple thing, must admit, im relatively OK with basics of access, just starting to get into more complex side. My question is this.

I have several tables, with my Main table being the final record, what I want to do is generate a specific reference number for the record, which can relate to our business system. This is in the format of vendor/accounts/record.

Vendor is picked from a pick-list box and record number is the unique record number, so they are fine. what I want is a pick list from a number of descriptions, this then completes the accounts section with an accounts ref.

The table is as follows

ACCNOM
* Accounts Nominal number
Description

As I say, I want a pick list to pick description, then in a seperate box, display the corresponding Accounts Nominal number, this sounds fairly easy, until you try do it.?

Any help at all would be appreciated.
Regards
JonathanW

 

[Jonathanw]

Anyone got any ideas, I need to complete this asap.
ray:

 

[mdmilner]

Very possible.

Making an assumption that when you built your form, you bound the table to it (or it could be bound to a SQL query based on the table)...
Insert something like this into tht AfterUpdate event for the picklist/combobox

Private Sub objectName_AfterUpdate()
Dim strVal As String

strVal = Me.objectName.Value & "'" & [fieldname] & "'"

End Sub

This dumps the text value of the combobox & the fieldname from your table into a variable after concatenating them together.

From here you'd end up using a recordset object to update the table value.

Here's a trick question though. Most of the time it's not a good idea to build fields in a table based on the contents of other fields. Assuming the value chosen from the picklist does appear in a record field, use a query instead. Queries can show the table contents and also build additional "fields" based on other fields. And, queries are 99% interchangeable with Tables. If you can use Table in one instance, most likely, a query works there also.

Mike

 

[Jonathanw]

I must confess, i was coming round to the idea that a query may well be better. Once the selection has been made, and the box for moving on is selected the whole section is locked, if they make a mistake, then they have to cancel and start again!

Cheers for help, I will report back when I have completed this project