Entry Problems onSubform with Linked Table

[bcooler]

I currently have a subform with the correct parent child relationship and everything works great. The subform allows the operator to add needed materials, quantities, etc for a given form entry.

Users now want a description added to the form, but one that autopopulates from a lookup in a description table once the user enters the part number.

I added the Excel linked table to the database and to the query that drives the subform's record source (query has no parameters, just links the 2 tables). Everything looks good until I actually go into the form. At that point, the subform no longer allows data entry anywhere in the form (datasheet view).

Things I know, but not sure if they affect me:

1.) The linked Excel table is 2MB and is currently on my hard drive (I will probably eventually move it to the network, but I know that will only slow things down). Do I just need to wait longer to enter data?

2.) I am warned that the created link is read only. That's OK with me, since I'm only using it as a lookup. However, I wonder if Access thinks I'm trying to potentially change the linked table by adding it into the subform query, so it just blocks me out.

 

[dhookom]

You should try remove the linked Excel table from the record source. Change your part number entry to a combo box that uses the linked Excel table as the Row Source like:

SELECT PartNum, PartNum & ": " & PartDescr
FROM LinkedExcelTable
ORDER BY PartNum

Set the column widths to
0;1"



Duane
Hook'D on Access
MS Access MVP

 

[bcooler]

After completion, this solution will show both the part number and description in the subform?

 

[dhookom]

They will both display in the combo box if you set it up correctly.

Duane
Hook'D on Access
MS Access MVP

 

[bcooler]

Man! That works great! I didn't know you could do that. In other areas of the same form I have accomplished the same thing (sort of) by adding adjacent text boxes to the combo box in question and having VBA populate them with the other columns' information in the "After Update Event". That was much harder than this.

If you have a couple more minutes, can you describe the areas of the SQL and how they relate to the query? Specifically, what are my limitations (and any other good examples you may recall) after the first comma? It appears you can do a lot more stuff than I expected and I want to see what other magic I can do here.

Thanks again!!!! God bless you!

 

[dhookom]

having VBA populate them with the other columns' information in the "After Update Event"

There is no need to do this with any code. You can set the control source of a text box to something like:
[tt][blue]
=cboMyCombo.Column(1)
[/blue][/tt]
This will display the second column value from the combo box. No code is required.

You can use all types of expressions and functions in your query. These are simple examples of common expressions:
[tt][blue]
FullName: [FirstName] & " " & [LastName]
CSZ: [City] & ", " & [State] & " " & [Zipcode]
Age: DateDiff("yyyy", [Bdate], Now())+ Int( Format(now(), "mmdd") < Format( [Bdate], "mmdd") )

[/blue][/tt]


Duane
Hook'D on Access
MS Access MVP

 

[bcooler]

Just keeps getting better and better.....

I like your 1st statement. I think it will help a lot. It seems a combination of my inexperience and believeing I could only choose a control source from the Properties Control Source pulldown led me to believe what you said wasn't even a choice.

The second part about the querys was really cool because it also tries to autofill as I type the part number. This is something I thought I would have to write a lot of code to accomplish. Thanks!