Txt box function inputting into a specific table.field

[aabh]

Hi there (Again)



I have a whole new problem:

In Access 2000 I have a form, bound to a table which tracks sessions. I am using a query to pull up enrolled students to make into a combo box pull down (So that my admin can enter a ton of these scheduled students quickly instead of having to search all 1,800 students we have had). The query comes from a student table.

Now, I need the students name AND ID to be passed on to the Session table from the single pulldown.

My Admin can't remember every student's ID number, and we can't put full last names on our shedules, so what I did was assemble the data into a single field ("1234-John S.") via a query then passed that query through to the pulldown. That saves just fine into the Session tables [Name] field.

But I need that ID number to save into the Session tables [ID] field, so I can get all of the students information later on.

So I set up a text box with the control source being the expression "Val([Student's Name and ID])". which should just strip the first 4 numbers, convert them back to a number and put it into the Session table's [ID] field...

Of course, if you put that into the box's control source, it's no longer putting the data into the [ID] field... I tried putting it into the Default Value field with no success. Same with the Before Update, After Update and some other fields on the properties box. I simply get "0" entered into that field on the table.

So where should I put that Val function so that it will perform the Val function and install it into the ID field?

If you could please use dummy-talk for me, I'm an Excel guy pretending to be an Access dude. I've got the hang of the interface, and I can make wild expressions... but I couldn't VB my way out of a paper bag...

Thanks!

---Aabh

[llkhoutx]

You've made a fundamental error in combining ID and Name into one field. That creates a non-normalized field which creates obvious issues. The combined field is not atomic, that is, contains one item of data.

A combo box "drop list" can display multiple fields, but only display one field in the combo box control. On the AfterUpdate event of a combo box, using VBA, one can store any or all of the columns of the row containing the displayed row of the combo box record source.

I have attached a sample database which illustrates the above.

Look at the properties of the combo box, especially
Record Source
Column Heads
Column Count
Column Widths
List Rows
List Width
Width

Also, look at the query in the "design view." The first column is the StudentID, but is hidden (column width = 0) in the combo box, but is repeated again in column 3 to be displayed in the combo box drop list.

[aabh]

Thank you for the response!

I actually can't read the DB in question (I have Access 2K-sorry about that: I'm on the list for upgrades, though! )

And I think I was totally unclear in my frustration, so I apologize there, too.

The problem isn't with the combo box, it's working just fine, the problem is in the text box which needs to take an option from the combo box (Depending on which one the user selects), perform an expression on it, and deposit the result into a field on the table.

So, the user selects "1029 Johnny Test" from the combo pull down. The text box needs to perform the expression "Val([Combo15])", display "1029" and then put "1029" into the "ID" field in the "Sessions" Table.

(I know, that's totally a "Why didn't you say so in the first place?" moment, sorry...)

So, the question is: in the properties box on Text 12 (the text box) which field do I put "Val([combo 15])" and which field do I put "Table.Sessions.ID"? As far as I can tell, they both need to go into the Control Source field... which means it can do one or the other...

[llkhoutx]

You say:

So, the user selects "1029 Johnny Test" from the combo pull down.  The text box needs to perform the expression "Val([Combo15])", display "1029" and then put "1029" into the "ID" field in the "Sessions" Table. 

Rather than use your Val function. It's not clear that it's working.

On the Combo15_AfterUpdate event, add the following code:

dim iPos as integer
iPos=instr(1," ",me.combo15) ' character position of the space
me.ID=left(me.combo15,iPos-1) 'string to the left of the space
me.Sessions=right(me.combo15,len(me.combo15)-iPos) ' string to the right of the space 

You cannot parse a combo box row, storing part of the row column in the combo box.

I don't think you need the Val function. my included code shows exactly what's being done. For many, a Val function is obscure.

[aabh]

Thanks for your help on this...

I went on ahead and put that in (I made a few changes, I didn't give the correct names (I'm not doing so good today, am I?), so I adjusted the names and now I'm getting errors (Actually, I got errors before, but I figured that was because I was stupid...)

So, let's get the entire thing straightened out (My fault- Doing most of this from memory wasn't a go idea- I have the database up on my second monitor as we type now-something I will do from now on).

The exact format of the field on the combo box (It's actually combo3) is:

149-Johnny P.
2102-Jane D.
1-Billy B.

The Form is linked to the "Session Database" table, with Combo3's pulldown getting it's information from a query called "OUR CURRENT STUDENTS FOR PULLDOWN"-Which only pulls up current students.

The exact name of the field on the Sessions Datbase table is "Student ID number"
The names can be put in the "Name" field

So, I edited your code as follows:

Dim iPos As Integer
iPos = InStr(1, "-", Me.Combo3) ' character position of the space
Me.Student ID number = Left(Me.Combo3, iPos - 1) 'string to the left of the space
Me.Name = Right(Me.Combo15, Len(Me.Combo15) - iPos) 'string to the right of the space

The problem is that I'm getting a compile error when I try to run it. It's getting hung up on "Student ID number" and saying "Expected end of string"

One last question: I need the ID number to be formatted as a number, not text (So it can be linked back to the main database later), that was why I was using "Val". Will this still happen?

[llkhoutx]

You've fallen into the trap for having spaces in control names.

Me.Student ID number = Left(Me.Combo3, iPos - 1) 'string to the left of the space 

should be

Me.[Student ID number] = Left(Me.Combo3, iPos - 1) 'string to the left of the space 

Moral, never, ever have spaces in control or field names. SQL queries, which you will encounter, present nightmares for spaces.

[aabh]

Yes... I guess if I had planned to be doing any programming on this database, I would have followed a more standard programming procedure. I wasn't resigned to learning VB when I started this DB (And I am learning! Just slowly...)

Okay corrections as met, Now I'm getting "Run-time error '5': Invalid Procedure call or argument" on

Me.[Student ID number] = Left(Me.Combo3, iPos - 1) 'string to the left of the space

Here's the complete code:

Private Sub Combo3_AfterUpdate()
Dim iPos As Integer
iPos = InStr(1, "-", Me.Combo3) ' character position of the space
Me.[Student ID number] = Left(Me.Combo3, iPos - 1) 'string to the left of the space
Me.Name = Right(Me.Combo3, Len(Me.Combo3) - iPos) 'string to the right of the space
End Sub

[llkhoutx]

Use a "breakpoint" and the "debugger" to test the values in the subroutine. Access "Help" tells how to do it.

[aabh]

Okay, I used the debugger and got a "Runtime Error 5: Invalid Procedure call or Argument"

on the line:

"Me.[Student ID number] = Left(Me.Combo3, iPos - 1) 'string to the left of the space"

It's not really telling me much more than that...

[llkhoutx]

Using the Debugger, mouse over the various variables to see what their respective values are.

[aabh]

Okay... I'm feeling like a real fool here... but mousing over the code does absolutely nothing... I've used the "Step" function (F8) and I get the error as soon as it enters Ln 22,Col1... but it won't go any further...

I CAN left mouse click on everything and select "Definition"... It seems to know what Left is (I figured as much ) and the Integer is cool... it's the Me.Combo3 that it has an issue with. On a whim, I changed it to [Combo3] and it said: "Cannot jump to 'Combo3' because it is in the library C:\Users\.... (the Database location here)' which is not currently referenced"

And, I feel like I'm not helping any by doing anythng without explicit instruction; which is a real blow to any belief that I'll ever be able to do this on my own.

[llkhoutx]

Your stepping right, put a breakpoint of LN 22 to stop there and mouse over the fields to see thier respective values.

From the error, your probably referenceing the combo box incorrectly. Test the combo box reference in the immediate window to see if you get an error there. Also, test potential modifications to the references.

It's very hard to tell what you are doing from your posts. From the number of posts in this thread, you are up to your eye balls in alligators. This is a very simple issue.

[aabh]

Well, I'm not sure Access 2000 has mouseover capability... as I hovered my mouse over every part of the code, non-code, heck I even moused over parts of the toolbar and there isn't a single mouseover balloon in sight.

However, I found a seperate solution:

Private Sub Combo3_AfterUpdate()
[StudentID] = Val([Combo3])

End Sub

With StudentID being a seperate text box with the ID field as its RecordSource

I know you were wanting to get away from the Val() operator, but I am concerned (As I started with Left() in my original code), that it would format the number as a string, which, when I go looking for '122' in the Master database, it wouldn't be able to see a match with the student with the id number of 122 (The Autonumber of the Student database).

So, this does the trick; It strips the numbers off the front, puts it into the database just fine (By placing it into that secondary text box which has the ID field as its record source), and formats it as a number. I verified that I can recover the data by linking the ID number with the Master DB table ID number. So THIS issue is solved (more alligators to come)

And, since this part of my program hasn't actually been put into use, I was able to reformat all names in the table with no spaces

So, though I didn't use your code (I'm sorry about that...It was cool code!), you did get me to be more serious about my programming style and I think I have a much better idea of what Access is doing and when!

llkhoutx, none of this was wasted time! I really needed to see what you were doing to get an idea of what was going on. So thank you a whole lot! I think I might be getting a clue!

(P.S. I love channel 11! )

[llkhoutx]

A better syntax for

[StudentID] = Val([Combo3]) 

is

me.StudentID = Val(me.Combo3) 

"Me" refuring to the current form.

You'll also find that, for integer values of combo3,

me.StudentID = me.Combo3 

Only use brackets when absolutely required, otherwise when you move on the SQL queries, you go nuts trying to balance same.

[aabh]

Will do! Thanks! (And you answered another of my questions "What is the me. thing for?" ) Okay, I'll change all of this right away! You are the best!