Simple Way to Auto Update a Form

[rlpenman1]

I have 1 table with 3 fields (orgcode),(manname),(manphone)
Orgcode is the primary. I have made a form for this table. I have set the orgcode field up as a combo box the other two are text boxes. I can not get the form to auto update when I change the combo box that is corresponding to that row of data. I have several working examples that for the life of me I can not find what is making them work. The examples I have do not have an active event or any VB coding with them. I am really over looking something here

Ryan

 

[JohnVogel]

rlpenman1,

You need to actually use a unbound combobox, and use textbox's for all three of the fields in your table. Optionally you can hide the actual orgcode textbox, and make it appear as if the combobox is actually bound to the other fields.

Example:

Here is how the form will (sorta) look in design mode:
+-------------------------------------------+
| comboboxlookup [xxxxxxxx](comboBox)
|
| orgcode [xxxxxxxx](TextBox)
| ManName [xxxxxxxx](TextBox)
| ManPhone[xxxxxxxx](TextBox)
|
+-------------------------------------------+

The properties of the combobox should be set as:
Row Source/Type: Table/Query
Row Source:SELECT TableName.orgcode FROM TableName;

And the properties for the form and other fields should be based on the table you are using (TableName, above).

Now, all you need to do is write the code:

Private Sub comboboxlookup_AfterUpdate()
Me.RecordsetClone.Findfirst "[orgcode] = '" & Me![comboboxlookup] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Private Sub Form_Current()
Me![comboboxlookup] = Me![orgcode]
End Sub


Hope this helps, if you need more info, let me know

John Vogel
john@thecompuwizard.com

http://www.thecompuwizard.com

 

[rlpenman1]

I am getting a syntax error while running this set up the the error is a missing operator in query expression TableName.OrgCode I am not sure which syntax is missing any help would be great thank you

Ryan

 

[rlpenman1]

My Row source is this
SELECT TableName.OrgCode FROM Team Info;

Ryan

 

[JohnVogel]

Change "TableName" to the actual name of the table that holds the data.

John Vogel
john@thecompuwizard.com

http://www.thecompuwizard.com

 

[rlpenman1]

I had just changed it back to tablename for the forum it is set to the real tablename in the program. So I still am getting the error.

 

[ESquared]

if

Team Info

is a table name, since it has a space, you must enclose it in brackets:

[Team Info]
[Team Info].[Field Name 1]
[Team Info].FieldName2

of course, with SELECT statements, if there's no ambiguity you can leave off the table qualifier. Instead of

SELECT [Table Name].FieldName from [Table Name]

You can just do:

SELECT FieldName from [Table Name]

but be careful when you start to use complex queries. To save typing in complex queries, especially when you join a table against itself, it's useful to use a short alias for the table:

SELECT T1.FieldName, T2.FieldName2 FROM [Some Ungodly Long and Awkward Table Name!!!] AS T1 INNER JOIN [Some Ungodly Long and Awkward Table Name!!!] AS T2 ON T1.KeyField1 = T2.KeyField2 ...

 

[JohnVogel]

How is it coming along, now? Did you get it working?

John Vogel
john@thecompuwizard.com

http://www.thecompuwizard.com

 

[rlpenman1]

John the code works great now thank you for your help. I know I will have many more questions soon again thank you

Ryan