Need dynamic unbound form content based on DB entries (1 Viewer)

[omnialive]

Hey guys! Been a while, I know! I have a peculiar need. What I am wanting is to be able to build dynamic form content/elements based on entries on a table. This is for a gym membership system. What this form is going to be used for is to allow the front desk to scan a membership card which then performs a search on the database. Part of this search is going to be on a table that contains various add-on classes, tanning sessions, etc that a member can add beyond their base membership.

On the left side of the form, I will display a picture of the member and their name. What I am wanting to do on the right side of the form is to build a dynamic list of the add-on perks they are enrolled in.

I know I could hard program elements on the form to be visible or invisible, but that would be static and leave gaps when I have to turn things off b/c that member isn't enrolled in that class, etc.

Is this even possible with Access and VBA? I know I could do this sort of thing with a webpage using PHP, PERL or whatever. I don't know the limits of Access Forms and VBA.

 

[MarkK]

A bound Access.Form raises a Current event after a record loads. If you handle that event you can check the value of various fields/controls on that form and then respond appropriately. This is a common event to use if you want to change the appearance of the user interface in response to the data in the current record.
Is that the kind of thing you are looking for?
Mark

 

[omnialive]

Not 100% sure I know what you mean by "bound Access.Form", but I do know how to trigger things based on events and when I find a new record. I also know how I can enable/disable certain fields on the form based on current record values. Might be my general ignorance of how Access works with bound forms.

Right now, I am treating every element on the form as unbound and then updating the form elements based on a query.

I will look more into bound forms to see how that might work. Sorry for my sophomoric level of understanding here. I don't really do Windows application development much at all.

A bound Access.Form raises a Current event after a record loads. If you handle that event you can check the value of various fields/controls on that form and then respond appropriately. This is a common event to use if you want to change the appearance of the user interface in response to the data in the current record.
Is that the kind of thing you are looking for?
Mark

 

[MarkK]

If you put something--a SQL statement, a query name, a table name--in the RecordSource property of an Access Form object in design view, then when the form opens it will attempt to create a recordset using that information. If it succeeds, the form is said to be bound to a record source, and it is called a bound form. A form that is not bound cannot raise a Current event. And if the form is bound then you are also able to bind controls, usually textboxes, to fields in that recordset. Fields bound to controls on a bound form update automatically and make it stupid easy to create a user interface to update data in a table, which is what is at the core of Access being so easy to use. In almost every other database environment, binding to a data source and getting data from it, and sending updates to it, is far more laborious.

Hope this helps.
Mark

 

[omnialive]

Actually, that helps a lot! Here is where I'm at: if the front desk scans the members card, then we are guaranteed a singleton match. However, if the member doesn't have their member card on them, the front desk can search by name. This might result in multiple records.

So, this bring up a question on whether or not I can dynamically change the RecordSource property of a subform that I call from a parent form. The workflow would be like this:

Front desk employee launches program and is presented with a Member Search form with three fields (MEMBER ID, LAST NAME and FIRST NAME) and a <SEARCH> button. I will put code in place to ensure they can only have data in one field type or the other, but not both.

Scenario A:If they scanned the member's card into the MEMBER ID field and clicked <SEARCH>, I would want to set the Member Profile form's RecordSource property to be something like this:

select * from MEMBERS where member_card_id = "Member Search".member_card_id.value

Scenario B: If they type data in any of the member name fields and click <SEARCH>, then I would want to set the Member Profile form's RecordSource to be something like:

select * from MEMBER where NAME_LAST_KEY = concat("*","Member Search".member_last_name.value,"*") and NAME_FIRST_KEY = concat("*","Member Search".member_first_name.value,"*")

Is this doable or should I just go with something else?

If you put something--a SQL statement, a query name, a table name--in the RecordSource property of an Access Form object in design view, then when the form opens it will attempt to create a recordset using that information. If it succeeds, the form is said to be bound to a record source, and it is called a bound form. A form that is not bound cannot raise a Current event. And if the form is bound then you are also able to bind controls, usually textboxes, to fields in that recordset. Fields bound to controls on a bound form update automatically and make it stupid easy to create a user interface to update data in a table, which is what is at the core of Access being so easy to use. In almost every other database environment, binding to a data source and getting data from it, and sending updates to it, is far more laborious.

Hope this helps.
Mark

 

[MarkK]

What I would be more likely to do is perform a search with whatever information is available, maybe a membership#, maybe the first few characters of a last name, whatever, and see how many records match that data, and there are essentially three cases...

Zero records: Not found error.
One Record: Preferred outcome. Nav to that record.
Many records: Show a popup, say a list box, but whatever, some interface that shows all the records that match, and allow the user to select one, and then execute the "One Record: Nav to that record" process, above.

I've got a selector tool you can download from my web site that might work for you. It's a bit complicated, but it's got some cool features too. I use it in my systems everywhere that a search like this is required.

Cheers,
Mark