How to Automatically Select a value in a Listbox

[nickw1001]

Hi,

I have been creating a database, very slowly for the last few days to try and administer invoices and jobs etc for my father's business. On the invoices form, I made a query which gives the next number for the invoice (by adding one to the previous invoice number) and so at the top of the form there is a listbox which displays this result of the query. However, in order for this value to be added to the field of the invoices table, I have to physically click the listbox for the value to be entered. How can I make it so that the value in my listbox is automatically selected and so that I don't have to click on it each time?

I have tried the listbox.itemdata(0) function and although it selects the value for me, I still have to click on it for it to be added to the table.

Any help would be greatly appreciated on this matter

Thanks in advance

Nik

 

[Tim L]

Why isn't Access being allowed to automatically create and put in the invoice number?

 

[ajetrumpet]

at the top of the form there is a listbox which displays this result of the query

Why a listbox here?? If you're creating invoices, might be better to put a text box here linked to your query.

How can I make it so that the value in my listbox is automatically selected and so that I don't have to click on it each time?

Under what event do you have your query statement listed??

 

[missinglinq]

As ajetrumpet said, why have the listbox? Why not simply

Private Sub Form_Current()
 If Me.NewRecord Then
  Me.InvoiceNumber = [I]YourQueryResults [/I]
 End If
End Sub

Linq

 

[Shep]

I might ask why so many people here have the extremely annoying and rude habit of, instead of giving a halfway helpful answer, question every method used by the person asking the question.

We should not care so much about the method the poster is using unless the poster is asking for help with the method.

I'd bet a paycheck that Nik has a perfectly sound reason for doing it the way he is - most importantly, because he wants to. Perhaps his form is unbound (an idea utterly foreign to many here). Perhaps he's a novice and he's implementing the best methods he can at his level. Perhaps he simply wants to. I don't see a problem with that. All of us have either been, or are, novices. Try to remember that.

I do see a problem with avoiding a poster's question and, instead, cutting him/her to ribbons with snotty, holier-than-thou attitudes. At least make an attempt to help the poster before suggesting other methods.

Nik,
To select the row in your ListBox programmatically:

You've gotten the thing to Select, but your ListBox does not yet have a default value. You can get the value of the bound column of the row selected and set that value as the default value of the ListBox with:

This assumes the row you want is the first row (index 0).

Me.ListBox = Me.ListBox.ItemData(0)

Then, call the AfterUpdate Event of the ListBox. I'll assume for now that you have some code in there. Call the event by typing in the full name of it, with the exception of the parenthesis.

For example, if your listbox is named lstInvoiceNumber, then the Sub Procedure for the AfterUpdate event would look something like this:

Private Sub lstInvoiceNumber_AfterUpdate()

Copy this piece, "lstInvoiceNumber_AfterUpdate" (w/o quotes) and paste it into the Sub you've been working with, after the line mentioned above.

To summarize:

Two lines of code needed:

Me.lstInvoiceNumber = Me.lstInvoiceNumber.ItemData(0)
lstInvoiceNumber_AfterUpdate

To add a bit of polish, you could set focus to the listbox first:

Me.lstInvoiceNumber.SetFocus
Me.lstInvoiceNumber = Me.lstInvoiceNumber.ItemData(0)
lstInvoiceNumber_AfterUpdate

If you need help with that AfterUpdate event, explore the .Column property of the ListBox. Figure out which column you need and use something like:

Dim lngInvoiceNumber as Long
lngInvoiceNumber = Me.lstInvoiceNumber.Column(0)

Replace the zero with the appropriate column number.

Finally, there are many perfectly acceptable ways to accomplish what you're doing. There are a few wrong ways, as well. The way you happen to be doing it is not necessarily wrong. On the contrary, it most likely is not.

Actually you could have found this information by searching the forum archives, and you'll get a lot of rolled eyes and whining from people here, for not having done that, as well. It's just as well that you don't actually ask a question (God forbid!), to be truthful. Just search the archives.

 

[boblarson]

I might ask why so many people here have the extremely annoying and rude habit of, instead of giving a halfway helpful answer, question every method used by the person asking the question.

We should not care so much about the method the poster is using unless the poster is asking for help with the method.

Shep:

I would ask you this question - would you say trying to steer someone who may be doing something in a very non-efficient and/or incorrect way being rude? If so, I think you miss the extra point of this forum, which we have tried to instill - that we are here to help, not just answer questions blindly and without caring, but to try to get people what they NEED, not necessarily what they THINK THEY NEED. What they think they need may not be actually what they need.

I know that, if I see someone who is going in a direction that is potentially troublesome, I want to help find out exactly what they are trying to accomplish so I can give them the best answer possible and even suggest another way of doing things that, due to limited experience (or other factors) may not have even thought of.

So, while some posters do seem to come across as rude in the way they say something, trying to get someone on the right track is not. So, I would hope that you would be able to see the benefit of this and that your original post about this would not be the way you see things now.

 

[Shep]

No Bob, I don't think it rude at all to help steer someone away from bad territory. The trouble is, far too many here assume every question is posted by someone who is already in bad territory. This attitude turns people off. I didn't see anything in the least troubling in the method being used by the poster, especially in light of the fact it's his first post. In my mind, it's his business if he wants to use a listbox. Worldwide networks are not going down if he uses it, and people aren't going to die.

 

[nickw1001]

Thanks for your help guys, esp. Shep.

To be perfectly honest I am a bit of a newbie and I got my listbox to display the result of my query using a wizard There is no wizard for doing so with a textbox, which is why I used a listbox as opposed to a textbox. I understand the basics of programming, but I guess it just takes time to know what all the different functions and properties mean and in what context to use them in, so for now at least I have been using the wizards If I had known how to make a textbox get the result of my query then update the table accordingly then I would have.

Someone asked the question of what event my listbox gets the result of the query from, the answer being none - the rowsource of the listbox is where it gets it from (set up by the wizard) and reads the following:

SELECT [TabJobs].[ID], [TabJobs].[Job Number], [TabJobs].[Job Address], [TabJobs].[Client] FROM TabJobs ORDER BY [Job Number] DESC;

I know I probably sound rather lame, but as Shep said, it's a case of me not being overly experienced in programming / access and just fumbling around until I get it to do something pretty close to what I want to, and that I simply don't know just yet how to do things properly.

And in answer to someone elses question, the reason that there is this seemingly over-complicated system of getting an invoice number instead of letting access do it for me is because the business is very established with a lot of previous invoices and invoice numbers that aren't modernised, so I did not know how to make the autonumber field start with a number that I wanted it to - ie 1087 as opposed to 1 which it wanted to, so I added the invoice number as a seperate field to the autonumber, which I could at least control to start or add a new number where I wanted to.

Anyway. thanks again for all your help

Nik

 

[missinglinq]

Perhaps his form is unbound (an idea utterly foreign to many here). Perhaps he's a novice and he's implementing the best methods he can at his level.

So what, Shep? The methods offered the OP work whether the form is bound or unbound! And I expect you're exactly right about him being a novice and using the the best methods he can. Only a novice would try to use this method. He's probably read that you can source a listbox from a query and doesn't realize that you can do the same thing with a textbox! That's why everyone here is trying to steer him in a better direction.

I do see a problem with avoiding a poster's question and, instead, cutting him/her to ribbons with snotty, holier-than-thou attitudes.

I've reread the posts and I see no eveidence of anyone being sarcastic or snotty, with the exception of yourself!

The trouble is, far too many here assume every question is posted by someone who is already in bad territory.

Perhaps that's because the vast majority of people who post here or on any other forum of this type are people who ARE already in "bad territory" as you say!

I didn't see anything in the least troubling in the method being used by the poster, especially in light of the fact it's his first post.

I think this is the most telling part of your posts! If you don't see a problem with using a listbox that you don't intend to use to make selections for, but only intend to use to "automatically fill" a textbox, then you obviously have a very poor grasp of application design. The Cardinal Rule is "Keep things as simple as possible and still get the job done!" I've also seen posts that you've originated on this site, some quite recently, and they only go to support the idea that you have a very poor grasp of Access, especially for someone who's used it for over six years, and probably shouldn't be telling others what they should be doing.

This forum has always been conducted in a civil manner, without flaming or name calling or ego trips. We often disagree on methodology and even on philosophy, but we do so in a gentlemanly fashion! To be quite blunt, if you'd exhibited this type of behavior on the forum that I moderate, you'd be issued a warning, and if you repeated the transgressiona second time you'd be banned from the site for life! This type of behavior is simply unconscionabe!

 

[Dave Eyley]

This isn't a question of whether to steer a person in the right direction it's more a case of how to steer a person in the right direction...

To say - "You could do what you're asking by.....but another way would be...."

Is better than saying, "Why are doing it like that? that's not a good way."

When I started posting here I felt there were a few condescending replies too.

So, I suppose the moral is - be helpful but be civil with it...

Dave Eyley

 

[missinglinq]

There's nothing "lame" in what you're trying to do, Nik! We're just trying to point you in a more efficient direction! The Wizards in Access really are handy tools! Not just for easily accomplishing the jobs they're designed for, but for learning and adaptation as well.

For example, after setting up your listbox, you can look behind the scenes (as you've obviously done) and see the SQL code that Access generated to perform the task in hand. This is a great way to learn Jet SQL. You could also now use this SQL statement to assign the value to your textbox.

Another question we see all the time is about creating a search function. The standard way to do this is to use the combobox wizard option to automatically set up retieving a record, but people often, for whatever reason, want to use a textbox and a command button. The simple answer? Use the combobox wizard to automate the task, look behind the scene to see how Access did this, then adapt that method to textbox/command button.

Good Luck on your project, and welcome to the forum!

 

[Tim L]

I did not know how to make the autonumber field start with a number that I wanted it to - ie 1087 as opposed to 1 which it wanted to

A quick search of the Access Off Line help file, using 'autonumber' as the criteria produced a list where the second item was:

Change the starting value of an AutoNumber field (MDB)

The same information is available On Line here.

A simple one-off solution that means you wouldn't have to use additional code for the rest of the life of the database.

Hope this helps, although, by the sounds of it we may have reached this solution a little too late... That said, I don't see any reason why you cannot adapt the method in the help file to add in the 'old/historical' records (note: this would need to be a one-time event, so that you do all of the historical invoices in a single go) - the main difference being that you'll be appending multiple records rather than a single one.

HTH

Tim

 

[Tim L]

I know I probably sound rather lame, but as Shep said, it's a case of me not being overly experienced in programming / access and just fumbling around until I get it to do something pretty close to what I want to, and that I simply don't know just yet how to do things properly.

No not lame. We all started somewhere and fumbling around is a good way to learn about Access. One of the people who frequents these forums used to have a signature about 'asking smart questions' unfortunately I don't have time to locate it right now. The gist of it was that if you ask the right question the first time then you will be more likely to get to a suitable answer more quickly (but it was better put and actually included hints/suggestions on how to ask better questions).

Your initial post was succint and to the point, both positive aspects. Unfortunately many others saw a possible pot hole and hoped to steer you around it, but didn't have sufficient information from the original question; don't blame yourself, you're new to the game. Hence the follow-up questions.

The best bit of advice that I can give here is to suggest that you carry on using the clear and succinct approach but to consider, whether there is any other information, such as 'why am I doing this this way', which might help people to answer your questions.

Good luck and hope to see you around more.

Tim

 

[Shep]

I concede the point that perhaps the poster was using a control not suited for the job. I will not, however, concede my point that snotty, holier-than-thou, and condescending attitudes prevail here. Anyone can deny it all they wish, but it won't change the fact. I think some of you need to take a vacation and drink lots of ale, and stop posting for a while. It's getting to you.

 

[thisisntwally]

I felt the need to chime in here, though i have no doubt everyone already understands my motivation in posting, and could communicate this for me more clearly and more efficiently . That being said I have found this forum to be, hands down, the most engaging and educational access forum out there. I would hate it if you guys let me blindly flounder around in some messy abnormal database of my own design simply because no one wanted to hurt my feelings. Its the real feedback and innovative thinking you guys do so well that keeps people posting here and which brings in new blood. Just as it would be tragic to lose anyone of our experts (seriously, you guys have no idea what lifesavers you are) we don't want to drive new posters away. Someone somewhere said that llamas are the experts of the future, and it would be equally sad if someone never came back because they felt they couldn't hack it, as it would if any of you were to leave because you don't feel appreciated or feel forced to hold back your input.

To start a new paragraph, don't hold back your input. Im inexperienced. I do stupid things, I attempt the blatently impossible, and quite often I have no idea what im talking about, let alone what you (all) are telling me to do. Please don't hold this against us, we're learning. Be nice, tell us what we're doing wrong, and gently guide us if you think we should be coming at a problem from a different angle. As i see it, telling someone they're all wrong and 'heres how you should do it' is remarkably similar to simply handing someone the code they need, instead of teaching them to make it themselves.

Sorry i've droned on, but everyone in this thread has given out advice that i've used somewhere or another and i wanted to be sure that you will all be here to guide us in the future. jeez this was cheezey

 

[Tim L]

thisisntwally,

I can't speak for the others, but I am sure their sentiments are the same, thanks for your support and I'm glad that you find the posts useful.

Speaking from the other side of the fence, I've been using Access for a few years now and know that I don't know half of what some of the people who post here know, and at work they call *me* the geek! I feel that as I do have some knowledge that it is sensible for me to reply where I feel that I can help, both from the learning experience (I often have to research how to solve the problems) and also because if it means that the people with greater knowledge don't have to answer the mundane questions then they will have more time to answer the more demanding questions. One or two of which I am sure that I have contributed, and will continue to contribute, to. We probably ought to call it something cheesy like 'pay it forward', lol.

Tim