Default Value Field

[Boots6]

I have an RFI and a Contacts table. I have an RFI form that has "To" and "From" look up fields connected back to my Contacts table. I am trying to add Title, Address, etc fields for the "To" and "From" fields using the Default Value property. I have created a text box with the Control Source set to "From" and I put the Default Value property to =[CONTACTS]![D_TITLE] but the box only shows the Contact ID for the record, not the title. What am I doing wrong?

 

[MazeWorX]

because you are looking up the id and not the description. Post all of the code

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Boots6]

That is all of the code. I'm pretty new to this. I got it to work by changing the "Title" box from a textbox to a combo box. Then it gave me the recordsource property. I'm now trying to add the Street Address, City, State, and Zip, but it is showing the record id again because it is a text box, not a combo box. How do I use the "To" combo box as the control source to display the address in a textbox? Sorry, I don't know if I'm going about this the wrong way, but any advice helps. Keep in mind, I'm pretty new. Thanks.

 

[Boots6]

I tried to attach this code to the text box:

Private Sub Text55_Click()
Dim strNewRecord As String
strNewRecord = "SELECT * FROM CONTACTS " _
& " WHERE D_COMPANY = '" _
& Me!cmboTO.Value & "'"
Me. = strNewRecord
End Sub

It says the last statement is wrong - "Compile Error" Expected: identifier or bracketed expression.

 

[MazeWorX]

concatenate the fields in a query something like

SELECT tblContacts.D_Company, [StreetAddress] & "," & [City] & "," & [State] & "," & [Zip] AS CustAddress
FROM tblContacts

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Boots6]

I'm going to do some reading on it when I get a chance. I know the expressions are in there somewhere. My Contacts Table has fields: "ID" (which is an auto-number), "D_NAME","D_TITLE",D_COMPANY","D_STREET","D_CITY" etc. The source of the form I created is the RFI Table. It has "To" and "From" fields as lookup fields going back to the "D_Name" field in the Contacts Table. I want to be able to select the D_Name from the "To" drop down and have the "D_COMPANY" etc auto-populate text fields I have created. Is there some sort of WHERE statement or something that can find the recordsource of the "To" combo box and populate the other text box fields from the Contacts table? Something I can put in the Control Source property that will look up the record in the "To" combo box and place the corresponding "D_COMPANY" in the textbox I've designated? Sorry, I hope that is clear. I thought I had this, then I stepped away from it for 2 days and it's like it all disappeared from my head. Thanks for your help.

 

[MazeWorX]

in the afterupdate event of your combo

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strSQL as String

Set db = Currentdb()

strSQL = " "SELECT * FROM CONTACTS " _
& " WHERE D_COMPANY = '" _
& Me!cmboTO.Value & "'""

Set rs = db.OpenRecordset(strSQL)

Me.TextControlName = rs!D_Company

etc

then refresh the form

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Boots6]

Thank you, I have that in but it still wants to refer to the ID field of the CONTACTS table. It says Run-time error '3075: Syntax error in string query expression 'D_COMPANY = '5'".

5 is the auto-number ID field for the person I have selected, but I must be missing one step to get to the other fields. Is it because the TO combo box is a look-up field? I appreciate your help.

 

[MazeWorX]

Change this

strSQL = " "SELECT * FROM CONTACTS " _
& " WHERE D_COMPANY = '" _
& Me!cmboTO.Value & "'""

To this

strSQL = "SELECT * FROM CONTACTS " _
& "WHERE D_COMPANY = " _
& Me!cmboTO.Value

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Boots6]

That gives me data type mismatch in criteria expression. Maybe I need to rethink my look-up field?

 

[Boots6]

The TO cell is a look-up field leading back to the CONTACTS table "D_NAME" field. The D_NAME field in CONTACTS is a Text field. Is this maybe causing some problems?

 

[MazeWorX]

yes is the data text or numeric?



HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Boots6]

The "TO" data is the Number data type, Field Size Long Integer. The "D_NAME" field from which the "TO" pulls its list is the Text data type. The rest of the Company information is the Text data type as well.

Everything in the CONTACTS table is the Text data type with the exception of the ID field, which is AutoNumber. The "TO" field is in the RFI table as the field type I described above.

 

[MazeWorX]

Try

strSQL = "SELECT * FROM CONTACTS " _
& "WHERE D_COMPANY = " _
& CStr(Me!cmboTO.Value)

this will convert to a string data type for comparisons




HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Boots6]

Thank you so much for all of your insight! I ended up setting the combo box to include all of the fields I wanted, then set the column widths to 0 for the categories I didn't want. I referenced the column # of the combo box to get the data into the text fields. I know that isn't the best way to go about it, but hopefully soon I will get time to do it properly. Thanks!