listbox refresh or requery

[wiklendt]

hi,

so i'm now doing a database for work. i'm essentially finished except for a couple of nagging things (which, honestly, i CAN live with if i have to).

the thing is, it's such the world's most basic databse that i'm embarrassed i can't get it right....

(please see attached zip)

so, i have a form, in which you can enter specimen details. on this form, i have a listbox, listing ALL the specimens in the database, so that when the user selects one, the details are displayed in the form.

i am working with Access 2007, btw, and this ddb was created in it from scratch.

now, my issues are

a) when i click on my "new specimen" button, i would like the listbox to have nothing selected. i have tried a number of commands/properties in VB, but i obviously don't know enough about listboxes to know what i can do with them.

b) my listbox is based on a query which pulls up the specimen name and name of the associated project for that specimen. now, when the query is run, it does not display specimens that are without a project... i've tried putting in criteria in the qry using ="" and <>"", but that doesn't work. i also tried =like "*", which also doesn't work.

the only thing i've been able to do to make sure no specimen is left out, is to make associated project as a required field. which MAY cause issues with some specimens when we're back-filling from old specimens that we may not know what project they belong to (e.g., student projects from 6 years ago or some such). i don't want to put "misc" or "all" or "unknown" i just want the field empty - and for it to display after a query run.

c) when i enter a new specimen, try as i might i can't get the listbox to requery or refresh. VB constantly tells me i'm using incorrect properties or can't find the tables or whatever. the only way i can force the listbox to accept the new record is to click on the refresh button, which "reopens" the whole form (including taking me back to the first specimen in the database).

the listbox rowsource is a query, yet Me!lstSpecimens.Requery causes errors. i have also tried ".Repaint" and a few combinations of code i thought might work. i wanted this to occur afer the new specimen name is entered (i.e. in the _AfterUpdate event), but nothing i do seems to work. i am missing something fundamental.

of course, AfterUpdate of txtSpecimen MAY trigger the txtProject required field (described in point (b) above), but i'll get rid of that once that issue is resolved.

right, that's all for now

cheers in advance.
agnieszka

 

[boblarson]

For a) - Just add this to your command button click event:
Me.lstSpecimens = Null

b) When in the query double-click on the link line between tables and change the selection to INCLUDE ALL RECORDS FROM tblSpecimens AND ONLY THOSE RECORDS FROM tblProjects WHERE THE JOINED FIELDS ARE EQUAL. But, that doesn't matter anyway because right now you can't enter a specimen without a project.

c) And in the form's AFTER UPDATE event you can put:
Me.lstSpecimens.Requery

 

[wiklendt]

my god boB, how quick and easy was that???? everything's fixed and working!! thank you thank you thankyou!!

:-D

agnieszka.

 

[boblarson]