I think the issue here is that it is simpler to set the record source using
the form & control properties instead of manually setting it in VBA. Let's
try this one step at a time. Pardon me if this seems over-simplified. I don't
know your table structure here, so let's assume for simplicity that you have
two tables.
Suppliers table having fields SupplierID (long integer) and SupplierName
(text)
SupplierCodes table having fields CodeID (text?), SupplierID (long integer)
& CodeDate (short date)
Create a form called SupplierCodes (Forms -> New -> Design View).
Right-click the form (click in the box above & to the left of the the form
header)
Click Properties
Click the Data tab
Paste this in the RecordSource property:
SELECT SupplierCodes.*, [Forms]![SupplierCodes]![SupplierChoice] AS Expr1
FROM SupplierCodes WHERE
(((SupplierCodes.SupplierID)=[Forms]![SupplierCodes]![SupplierChoice]) AND
(([Forms]![SupplierCodes]![SupplierChoice]) Is Not Null)) OR
((([Forms]![SupplierCodes]![SupplierChoice]) Is Null)) ORDER BY
SupplierCodes.CodeDate;
Click the Format tab.
Double-click in the Default View box until it says Continuous Forms.
In the form DETAIL section, create two text boxes (side-by-side, not one
above another: CodeID and Code Date (put the labels for these above the boxes
in the form header, not in the detail section)
Right-click the CodeID text box.
Click Properties
Click the Other tab
Enter the name CodeID
Click the Data tab
Click the drop-down box to the right of ControlSource and select CodeID.
Do the same for CodeDate (and format it as a short date)
In the form HEADER (if the header does not appear, right-click the form and
click Form Header/Footer), create a combo box called SupplierChoice.
Cancel the combo box creation wizard if it pops up.
Right-click the combo box.
Click the Other tab
Name it SupplierChoice
Click the Data tab
Leave the control source blank here!
Paste this in the Row Source:
SELECT Suppliers.SupplierID, Suppliers.SupplierName FROM Suppliers ORDER BY
Suppliers.SupplierName;
Click the Event tab
Double-click in the AfterUpdate box until it says [Event Procedure]
Click the ellipsis to the right.
Enter Me.Requery in the code module so that the sub looks like this:
Private Sub SupplierChoice_AfterUpdate()
Me.Requery
End Sub
Click the Format tab
Set the Column count to 2
Set the column widths to 2,0
Here is the net result. When you open the form, it will show all
SupplierCode records, sorted by CodeDate only. As soon as you pick an entry
for SupplierChoice, the list will be limited to those entries having that
SupplierID. The actual SupplierName, though, is what appears in the
SupplierChoice box.
Take a look at the form's record source via its properties, and you can see
how you could easily add a second CodeChoice box in the header so that the
user could filter to one code, one supplier, or one code and one supplier
(and potentially even a date range).
This is basically a lookup form, although it can easily be modified to be an
input form also.
tina said:
Thanks Brian
The Ideas is just what I wanted
though I cannot quite get it to work please excuse if i'm being a idiot
I can't seem to get recordsource of form correct
i tried
Forms![SELECT BATCH TEST].RecordSource = "SELECT * FROM [SELECT CODE BATCH
STATUS]" & "WHERE supplier =supplierchoice"
but this asks for supplierchoice parameter value to be entered aswell as
code value (the first prompt)
select batch test is open form and select code batch status is query it is
based on
I am used to writing vb in excel not Access so find somethings like
recordsource bit confusing tried help but still unsure
sorry to ask for more help but any would be much appreciated
Thanks
Tina
Brian said:
Have an UNBOUND combo box called SupplierChoice in the form's header. Then,
make the RecordSource of the form (i.e. detail section) include something
like this:
Select * from Table1 Where Supplier = [Forms]![Form1]![SupplierChoice]
Requery the form in SupplierChoice_AfterUpdate to apply the filter.
:
Hi
I have a form which when opens requires user to enter code at prompt the
form shows code desc supplier and for entering batch number and result. the
form shows history of code but sometimes their is more than one supplier so I
would like a second prompt requesting supplier ideally from a list so only
that supplier history is shown for the code
I am unsure of the best way to do this any hints would be much appreciated
Thanks
Tina
