Dynamically generate text in a form text box

[ssmith147]

Hi,

I'm somewhat familiar with access and vb programming (I can read someone else's code, for the most part), but I'm still very green when it comes to creating solutions for my own needs.

I'm trying to create a db for a friend's business. I have two issues that I'm wrestling with. First is how to dynamically create a text box on a form.

I'm building a form that relates information between two tables (A, B). Table B is the source of the majority of the data on the form, while table A includes customer contact info (the tables are linked in a relationship).

I'm using a combo box to select customer information from table A. The cbx includes LastName, FirstName MI. and DateofBirth (in that arrangement). Hidden values include the customer ID (primary key) and date when the customer was entered into the db.

I want to dynamically generate a boilerplate with the customer's address and phone number right below the cbx. The information will be specifically used for this form and the current user. Any subsequent review of the data will be generated in a report that will automatically populate from both tables (I haven't created this report yet, though, so I may be posting again :) ).

I'm assuming the solution will be a vb code based on some event (After Update?), and will define the caption property. Any help on this one?

Hopefully, the second problem is easier. There is a checkbox on Table A which must be set to True. The box defines whether or not an insurance liability form has been completed before specific sales are completed (paint ball markers JFYI). This information is best stored on Table A (customer info) than on Table B (sales info) because one customer may buy several markers over a period of time.

I want to display the status of the checkbox on the form. I also want the user to be able to update Table A by using this checkbox. I found a workaround for this problem on another form where I used the tag property of the cbx and I created another textbox and an "Update" button which could update Table A based on the information in the second text box (does that make sense?).

However, that was a textbox, not a checkbox, so I'm not sure if it will work the same. Assuming it will work, and since there is still only one item that I must be able to change in Table A from this form, should I just do the same thing? Or, is there a more direct and efficient way to accomplish this?

Thanks in advance for any help offered.

[Zwoker]

Hi,

Looking only at your first question:

Having a quick Google for creating objects in VBA for MS Access I found some code that works, but it seems that it can't be used to update the form you are running from... So perhaps this doesn't help you.

Does anyone know how to do this on the current form you have the code running in?

An alternative may be to have the text boxes already created on your form, but have the Visible property set to No until you need to display them.

Anyway, here is the code for what it is worth. It is assumed that the code is called from a form other than the one that the code references (test_form). I tried this (in MS Access 2003) and it worked fine.

Private Sub MakeATextBox()
  ' Creates a text box of the name "tbxTest" using the control source "txtTest"
  ' The text box is 1 in wide, 2 inches high, placed 0.5 inches from the
  ' top and left of the detail
 
  Const TWIPS As Integer = 1440 ' text box dimensions are in twips
  Dim ctl As Control
  Dim intLeft As Integer
  Dim intTop As Integer
  Dim intWidth As Integer
  Dim intHeight As Integer
  intLeft = 0.5 * TWIPS
  intTop = 0.5 * TWIPS
  intWidth = 1 * TWIPS
  intHeight = 2 * TWIPS
 
  DoCmd.OpenForm "test_form", acDesign
  Set ctl = CreateControl("test_form", acTextBox, acDetail, , "txtDesc1", intLeft, intTop, intWidth, intHeight)
 
  ctl.Name = "tbxTest"
  DoCmd.Close acForm, "test_form", acSaveYes
End Sub
 
' Call the code from where ever it is needed.
MakeATextBox

[ssmith147]

I see what the code is trying to do, and it makes sense. I'll have to play with it a little bit tomorrow. I'm thinking the command should be able to generate the box on the same form using the 'Me' call, but I'll find out.

Also, I was unclear about how I was going to run this. The information box will already be in place on the form, but the information presented in it will be left blank until a selection is made from the combo box. I will definately try settig the visible property, though, as this may work very simply.

My major concern is whether or not vb can dynamically create the text in the same form (which still seems to be in question) and if it can do so based on the primary key data of a foreign table.

Thanks for the code- its a good place for me to start. If anyone can gather more info on this or the second question, I would still appreciate more help.