Query not combining and filtering values based on a form

Okay, go to qryMultiSelect and open in Design Mode. You will see the table Equipement_Spec, double the word Description and it will drop down to the end in the grid section. close and save.

While you're doing that I am typing up the instructions to get it on the Form.
 
To add Description to the Form...

1. Open the Form frmMultiSelect in Design Mode
2. From the Ribbon select Add Existing Fields and from the window that opens click (and hold) the word Description and drag under the Detail bar and then release
3. Click on the Label and delete then reposition the field (Description) putting the newly added Description field where you want it
4. In the Properties window make sure to set Enabled = No and Locked = Yes
5. Now close and save the Form and then reopen to make sure everything is the way you want it.
 
That's it! Added Description field to qryMultiSelect. Then added it to the detail section of frmMultiSelect and saved all. Opened the form back up and it works perfectly! Thank you!!!

Just a couple more questions and I promise I will leave you alone and stop asking questions.
Do you know why when I set the filter and create a report out of the results (in order to print it) the supplier name field has the autonumber for that respective supplier and not the name? But when I create a report of the unfiltered results the supplier name comes through instead of the number.


"Just leave it out, use Camel Back and your field names will be very easy to type and read! (Thanks to Victor R. for the suggested amendment.)"
If I can't use spaces or underscores in field names what does using Camel Back mean? My problem is that I need to import Excel files for data sometimes and need to have my field names and the column names in the Excel sheet to be the same in order to import it.


btw before I forget I kept Enabled=Yes in the property sheet of Picture field so you can still click on the link of the picture.
 
Ask as many questions as you like...

I would need to see the SQL for that report to answer that question, but I suspect it is becase one is pulling from the talbe which only has the Supplier ID and the other has the Supplier Name.

Camel Back means first letter of each word is capitalized, so... GinaWhipp (this is the Camel Back one), instead of Gina Whipp or Gina_Whipp or GIna/Whipp. Oh, well since I use automation field names not an issue. However, if I had to do it that way, I would import into a Temp table that has all those wacky field names and then run an APPEND query in the background and send it to my properly named tables/fields and then delete the contents of the Temp table to be ready for the next set of data.

Yep, forgot about the whole click to see the image thing, good catch!
 
Where would I find the SQL code for you to look at when I run a filtered and unfiltered Report?

The one thing I can see different between the two reports is in the property sheet, the Row Source for Supplier Name field in a report:

Unfiltered query (which displays the supplier name):
SELECT [Supplier].[Supplier_ID], [Supplier].[Supplier_Name] FROM Supplier ORDER BY [Supplier_Name];

Filtered query (which only displays the autonumber instead of the name):
blank


"Oh, well since I use automation field names not an issue."

What does this mean?

"However, if I had to do it that way, I would import into a Temp table that has all those wacky field names and then run an APPEND query in the background and send it to my properly named tables/fields and then delete the contents of the Temp table to be ready for the next set of data."

Something I need to try and see how it works. Because I have been trying to match the field names between the db and the Excel spreadsheet in order for the import to work. And as you can imagine it is a real pain and usually does not work to well. So if there is a better way I would love to use it.
 
Oh, then you need to copy that SELECT line to the corresponding Row Source in the Filtere QUery, that should fix that issue!

Hmm, automation, an example of automation...

Code:
Public Function ImportOrders()
    Dim lngColumn As Long
    Dim xlx As Object
    Dim xlw As Object, xls As Object, xlc As Object
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    
    Set xlx = CreateObject("Excel.Application")
    'xlx.Visible = True
    Set xlw = xlx.Workbooks.Open(Me.txtImportPath & Me.cboDirectoryName & "\" & Me.txtFileName)
    'Set xls = xlw.Worksheets(1)
    Set xls = xlw.Worksheets(Me.cboWorksheetName.Value)
    Set xlc = xls.Range("A2")
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("tblOrderImport", dbOpenDynaset, dbSeeChanges)
    
       UpdateUser ("Importing " & xls.usedrange.rows.Count - 1 & " Orders from Orders spreadsheet...")
        
        Do While xlc.Value
        rst.AddNew
        
        'Always minus 1 field/column extra then the column count of the Excel spreadsheet
        For lngColumn = 0 To rst.Fields.Count - 6
            rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
        Next lngColumn
        
        rst.Update
        Set xlc = xlc.Offset(1, 0)
        Loop
        
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    Set xlc = Nothing
    Set xls = Nothing
    xlw.Close False
    Set xlw = Nothing
    xlx.Close
    Set xlx = Nothing
    xlx.Quit
End Function

I tell Access what row to start at and then I put in a Temp table to *clean* with some APPEND and UPDATE queries and then push to the Live table(s). I just need to make sure I have the correct column count but column names are irrelevant since I can start at whatever row I want.
 
Okay, I'm done for the night (at least night where I am). I'll address any questions tomorrow! :)
 
"Oh, then you need to copy that SELECT line to the corresponding Row Source in the Filtere QUery, that should fix that issue!"
I'm sorry to say that it didn't work :( Still only the AutoNumber not the name.

Automation sounds great but I think I will leave that for when I get a little more experience.
 
Well, I knew this would happen. I need to make another query just like the one you created for me but with different fields to query on.
I reread how you explained how you created the code, but I'm just not understanding how I can adapt what you created to this new query :confused:

I already created the new query for this and have setup a basic structure of the new form (based on what you created).
 
Hmm, dealing with Post 48 first. Is this Combo Box on the Report
 
Yes, it's on the report. I'm making a report out of the results of the form when it's filtered.
 
Have been in meeting since AM :(, will be back when I get out of meeting! (They actually need me to pay attention for this one.)
 
Since it's a Combo Box on the Report you need to go to the Properties window of the Cmbo Box and set the Column Count to 1 and the Column Widths to 0";1". Then try again and see if the name shows.

I am lost with Post 49 please tell me which post you are answering there.
 
Post 49 was not a reply. I need to make another form just like the one you created but with different fields. And I've tried to understand how you created the first one but am not able to adapt that form to the new form I need to make. I've already created the query and have setup a basic structure of the new form (based on what you created). But I'm just at a loss as to how to adapt your form.
 
Oh, okay well that's easy have you created the Combo Boxes yet? Are they the same or did you make different ones?
 
Sort of, I only need two of them: one to search on Supplier (so that's just an unbound combo box that has a look up to the Supplier table to get it's values, correct?) but the other combo box would need to be a combination (two columns) of the Description along with the Madeup Code for that description. And again this query is searching in the Equipment table with the results showing Project (but also project Month and project Year which would need to be pulled from Project table), Supplier, location/aff, description, cost and quantity purchase.
 
In the form (frmSupplierDescriptionCodeqry) I now have two unbound combo boxes (cboSupplier and cboEquipment).

cboSupplier has it's row source as:
SELECT [Supplier].[Supplier_ID], [Supplier].[Supplier_Name] FROM Supplier ORDER BY [Supplier_Name];

cboEquipment has it's row source as:
SELECT [Test code Query].[Madeup code], [Test code Query].[Description] FROM [Test code Query] ORDER BY [Madeup code];
 
Okay, why do you need both columns for the second Combo Box? Just seems like that will make that combo box extremely long. Should also note that you will only be searching by description, the second column with the madeup information will be for show only.
 
Okay, I saw your post just as I posted mine and it looks like you want to search by MadeUp code, is that correct?
 
Ok I was told that 'they' would like to search by Supplier name, Description and Madeup Code. I just combined Description and Madeup Code in the same drop down because they are one in the same. It didn't make sense to me to have Description and Madeup Code in two different combo boxes because it would be like have two combo boxes for Supplier name.
 

Users who are viewing this thread

Back
Top Bottom