box based uppon unbound text box

[vicsaccess]

ok, this seems like it would be simple and i'm probably 90% there but I need some help. I have a split database and each FE is unique to a line(machine). currently I have the records sorting on each FE with a applyfilter.,"lineID=11" and in the default value of this lineID box I have a default value of 11. for each FE I have to change the default value in the property and then go into the VBA and change the value of the line ID. what I would like to do is put an unbound text box on the screen not visible that I could put the lineID number and everything is based on that, sounds simple. I've set the default value of the unbound text box called "defaultline" to the 11 now I just need to get the bound box LineID to default to it and then have "LineID" sort only the records from that line. I started with
DoCmd.Applyfilter,"lineID=11" which worked great
not trying
me.lineID=me.defaultline


DoCmd.Applyfilter,"lineID"

seems simple but as i'm learning as I go i'm sure i'm missing something

[ItsMe]

The best approach might be to base your default form on a query. You can reference the textbox control in your query as a parameter.

While in design view of your query object, type the following into the criteria field within the grid. type Forms and then hit the tab key. Then type ! and select a form name. Then Tab key and ! and control name.

[vicsaccess]

thanks ItsMe, referencing a form in a query is something I do many times in my reports and queries. so what you are saying is
a query based on a table with a where statement pulling from my unbound text box.
a form based upon the query.
that just seems like an extra step if I could just get the lineID to default to the DefaultLine unbound box would be simpler.

[ItsMe]

...
get the lineID to default to
...

Not sure I follow. Isn't lineID a field in a table? The way you describe it here, you want to write data to the table. Earlier, you were explaining a desire to apply a Filter to a Form's Recordset. I suggest you retrieve the one record desired by applying a WHERE statement. You can do it via a query or apply it directly to the Form's Recordsource.

If you want t retrieve all of the records and then filter a single record, it would be something like ...
Me.FilterOn = False
Me.Filter = ""
Me.Filter = "lineID = " & Me.NameOfControl.Value
Me.FilterOn = True

[vicsaccess]

yes, the lineid is a field in the table, each FE database is specific to a line, what I didn't state now that I read it is that each line can scroll thru their records with forward and back buttons. when they scroll thru the records I need the operator to only see the entries that they have entered thru that line which is saved as "lineid". I am currently using the DoCmd.applyfilter,"lineid=11" and the lineid box's property is defaulted to 11. I would like to be able to put an unbound box on the form, put 11 in it and have only records from line 11 come up when they scroll. that way when I give someone a new FE all I have to do is put the line value in the box and all the records will go off that box.

[ItsMe]

I was taking 'default to' as meaning assign a value to.

As I mentioned in my previous post, you can use code to affect change to the form's Filter property. You can do this for several records or for one record. You can certainly use DoCmd to affect change to the Filter property.

Besides the reason previously stated, I recommend avoiding the Filter property in case the User manages to remove the Filter. Of course, there are ways to prevent the user from removing the filter, but it is not easy to guard against all options.

[vicsaccess]

vicsaccess.zip
thank you for your help, I still wonder if we are talking about the same thing so I have attached a rough copy of the DB. hopefully this will clear up my confusion.

[ItsMe]

I took a look at your example and made some changes to illustrate the two options I referred to. I added two Sub-Procedures and I have one of the two called from the Form's load event. The procedure that is called will apply a RecordSource to the Form. The other procedure is for the Filter property.

In order to test the two sub procedures, I enabled the Navigation Buttons of the form. I did this so I could see the record count and navigate from the first record to the next - last.

The two sub procedures are named applyRecordset and applyFilter.

Be sure to read the comments I provided. I made some additional changes to properties while in design view of the From.

[vicsaccess]

again, thank you. sometimes I just need a quick answer but most of the time I'm needing direction to help me learn to do it on my own and this helps a lot. if I'm reading this correct the second option of using the record source is best, its essentially using a query sql as the record source filtering the lineID but where is the connection between linedefault and p_lineid?

[ItsMe]

In the example I provided the value of the unbound control is evaluated in a gated check.

If Me.lineDefault.Value > 0 Then
    lngLineID = Me.lineDefault.Value
Else

From there, you can use the variable, lngLineID , as an argument.

When you call a procedure or a function you may need to provide an argument. If the sub or the function has a parameter, you will need to provide an argument. The exception is if the parameter is an optional parameter.

applyRecordset lngLineID

In calling the sub procedure above, there is the requirement of one argument to be provided for the parameter, p_lineID.

Private Sub applyRecordset(ByVal p_lineID As Long)

When you build code to call a sub or a function, you can use the hints provided by the VBA editor to determine what the parameters are and what data types the arguments provided need to be. For instance, as you type the name of the sub and then hit the space bar on your keyboard, the hints for the parameters, if any, will appear.

Give it a try. Type applyRecordset and then hit the space key.

[vicsaccess]

thank you, that makes sense