I need help to Query via Form Value on Event 1

[abhodges]

Form: ServerDetail
Table1: ServerDB
Table2: MasterAppList
Link: ServerName

I need to populate textbox or listbox with all matching records from MasterAppList based on ServerName, and have it update, everytime ServerName changes in the ServerDetail form.

NOTE: Not all servers have a record in the MasterAppList table, and some servers have multiple records.

I have a query that works for the initial record, but I don't know how to get it to update.

SELECT *
FROM MasterAppList
WHERE (([Forms]![ServerDetail].[ServerName])=[MasterAppList].[Server Name]);

I had a subform that would successfully accomplish this for the form view, but I have an external app that pulls data from this DB and it could not collect the data in the subform.

And, in case you can't tell yet, this is not my primary job function. So if you are able to offer any help, please use little words. ;^)

Thanks,
Bruce

 

[hermanlaksko]

Several problems here.....
1. Are you using a textbox or a listbox, big difference.
2. Maby I am misunderstanding your Q, but what are you trying to update. Looks to me like you only want to view records.
3. External app should not get data from a subform, only from a table or query.

Please let us have a few more details to clerify your Q.

Herman
Say no to macros

 

[Remou]

A few additions to hermanlaksko's comments.
I think you must use a listbox, because "some servers have multiple records". You can get the listbox to update by editting the query it is based on to make it reference the textbox on the form that has the server name or ID, as you have already noted. The other way around is more usual and a key field can be very important, also, specifying the fields is a good idea:
[tt]SELECT MasterAppList.AppID, MasterAppList.AppName
FROM MasterAppList
WHERE MasterAppList.[Server Name]=[Forms]![ServerDetail].[ServerName][/tt]

To get this to change for each record, you need to use the On Current event:

Private Sub Form_Current()
Me.[i]lstApps[/i].Requery
End Sub

PS I note that you have spaces in your field names, this can cause a lot of work. It is often best to use a prefix for the names of controls, txtServerName, for example.

 

[abhodges]

>hermanlaksko (Programmer) 31 Mar 06 1:42
>
>1. Are you using a textbox or a listbox...
>2. ...what are you trying to update.
>3. External app should not get data from a subform, only from a table or query.

1. I have been attempting to get a listbox to work, but without much success. So whatever works is fine with me.

2. Maybe update isn't the correct term here. I need to REFRESH the data displayed in the listbox based on ServerName, everytime the ServerName changes on the ServerDetail form.

3. I just added this info to indicate a limitation I am working with. That app is out of scope for me.

 

[abhodges]

Remou,

I don't follow exactly what your saying about the key field. However, I changed the WHERE statement per your example and added the Requery sub and the form now works just as I wanted it to.

Thanks for the tips on the spaces and SELECTions.

And a BIG THANKS for providing a solution.

Bruce