Populating Text Box

[MAPetry]

I have a form for a fax cover table.
The user types in the company name and populates the contact and fax number fields with data for the company. Only problem I get the contact part to work but for some reason can not get the fax field to populate. Contact info is in a companycontacts table and fax number is in main company table. Code i have so far is
FaxCompany_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT DISTINCTROW [Contacts].[CONID], [Contacts].[CONFirstName]" & "&" & "' '" & "&" & "[Contacts].[CONLastName] FROM [Contacts] WHERE ((([Contacts].[CONCompany]) = " & Me.FaxCompany & &quot);"
Me.CONID.RowSource = strSQL
Me.CONID.Requery

The above works great have no problem but below will notwork


Private Sub FaxCompany_AfterUpdate2()
Dim strFax As String
strFax = "SELECT DISTINCTROW [Companies].[Fax] FROM [Companies] WHERE ((([[Companies].[ID]) = " & Me.FaxCompany & &quot);"
Me.Fax.Rowsource = strFax


Any suggestions? Thanks in advance Mary Ann

 

[RickSpr]

You have one too many left brackets at: [[Companies].[ID]
Was that just a typo, or did you cut and paste it?

 

[MAPetry]

I deleted the bracket but it still doesn't work. I have tried everything and it seems like this should be a simple code. Is there any way you can use an INTO command in Access.
Bottom line I want to default the fax value to the fax number for me.faxcompany. I have set my fax field up as a combo and as a text box (above is as a combo) and either way I get back all the fax numbers in the database. Is there something about Access that won't let you have two AfterUpdteprocedures on the same field?

 

[MAPetry]

OK, I figured it out I had the field name incorrect in the field propoeties box.
Just one more thing..... How do I automatically make it put number in box now it is in a combo box and you have to click to get number. I tried the text box thing but my code doesn't work then. either I need to rewrite my code to default value to a text Box (HOW) or change a field property on combo box to auto insert into visible box not drop down.
Any suggestions???

 

[RickSpr]

No, you can't have more than one event procedure for a single event. I didn't notice you'd done this in your original post.

When the AfterUpdate event occurs on a control, Access' own internal logic looks for a procedure named <controlname>_AfterUpdate. If it finds it, it executes it. It doesn't check for any additional, similar names.

That's not a problem, though. You can just take the last two statements out of the AfterUpdate2 procedure and put them at the end of the AfterUpdate procedure. There's nothing that says you can't change the values of multiple controls from within the same event procedure. Since you seem to be saying you got it to work, I guess you aleady did that, right?

There's no reason you can't use a text box if you want to. If it didn't work, you must have another syntax error or some property set wrong. Did you get an error message, or did the text box just stay blank? If you got an error message, tell me what it is. (Hint: Always identify error messages--they're the best shortcuts to debugging.) If not, what was the name of the text box and what statement did you use to try to set it? Also, make sure the text box is unbound (Control Source property is blank). Rick Sprague