Form with Combobox to search record

Rixx

Registered User.
Local time
Today, 18:42
Joined
Mar 3, 2005
Messages
25
Hi,

I am trying to build a form that allows the user to INPUT stock as it arrives. This is simple with a product form that shows all the products in the table. I just go to the quantity field and change it.

But, I want to create a 'search' function in the field. I want to allow the user to type in the Product number. Then the Product name, Product Price and Quantity is AUTOMATICALLY displayed.

The user can then change this value (items in stock).

Thats pretty much the jisst of what I wanna do.

Can anyone help?

Thanks in advance.
 
dim mySQL as string
dim r as dao.recordset

Procedure txtProdID_OnExit()
mySQL = "Select fldProdId, fldProdName, fldQuantity From <tblName> Where fldProdID =" & me!txtProdID
set r = currentdb().openrecordset(mysql,dbopendynaset)
if r.recordcount > 0 then
me!txtProdname= r!fldProdname
....
....
endif
r.close
set r = nothing

end sub

or you can use Dlookup funtion to return the values

txtProdname = Dlookup("[fldProdname]","<table name","[fldProdid]"=me!txtProdid)
....
....

Hope this work for you....
 
Thanks for a quick reply!

But I am a newbie at access, could you tell me where to put this code?

I have created a Query that enables me to ask the user for a parameter value (productID), then it displays the record and allows me to change the quantity.

I have also created a form that uses this, but I get the same popup window as in a query. And it also only allows me to do this one at a time!

Is there a way I can incorporate this 'popup' on the form so that a user just has to enter the ProductID and the record is displayed. I can change the quantity and then continue onto the next form (Continuous form view)

Thanks again
 
Hope this one will help..See attached sample file
 

Attachments

Hi mate,

Your example helped to an extent :cool:

But, I think I can better explain myself by showing you what I am doing! I have attached the small db. The "Items IN" form shows the combo box and everything works fine. You select the ItemID and it all comes up. But I run into problems when it needs to save the value!

Alternatively the "Items Query" form is there too. This works fine with no problems but I am having to activate the query each time. This isnt practicle. Is there a way of embedding the original query popup within the form? So I can let say ammend multiple records?

Hope this isnt too taxing! I appreciate the help.

Thanks
 

Attachments

I made some revision on your database.

1. InForm - I removed the Recordsource value of the form and set all the fields controlsource to Unbound. I added also code on the OnExit of the Quantity field. I also added three variables

2. QueryForm - I created a new form and have your original form be a subform

Hope this will help...
 

Attachments

Thanks a lot mate!

Your revisions have definitly deemed to be useful! Its sods law that I went and changed what I needed to do in the end! :( Only to get myself into new and other problems *sigh*

I hope you can mull over this problem (as it is related to the previous 1) and fingers crossed I can nearly be done with this database! I have previously posted it and can be found here:

http://www.access-programmers.co.uk/forums/showthread.php?t=82667

Thanks for all your help
 
Last edited:
Adevera,

In the file you edited and sent back to me.. How did you manage to get rid of the query prompt ([forms]![form1]![combo0]) when accessing the form?

When I try to do this on my own db, the form asks me for the value in a dialogue. But in your database, I can go straight in and type this value (that I would usually type in the popup) in the combo box (comboO)?

:confused: :confused:
 

Users who are viewing this thread

Back
Top Bottom