I was told I should look up what RECORDSET is otherwise I wouldn't get it. So I didn't but I'm still not getting anywhere. I'm new to this but willing to learn.
Combo boxes:
companycell, companyflux, companyribbon
fields on the query and table that i'm looking up are:
[company name] (from table: flux information and query: sales) and [ribbon company] (from table: ribbon information and query: sales both hold text
Here is my code:
Expand|Select|Wrap|Line Numbers
- Private Sub Findbtm_Click()
- Dim rs1 As DAO.Recordset
- Dim rs2 As DAO.Recordset
- 'Attach recordsets to queries
- Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM [Flux Information] WHERE [Company Name]" & "='" & [Companyflux] & "'", dbOpenSnapshot)
- Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM [Ribbon Information] WHERE [Ribbon Company]" & "='" & [CompanyRibbon] & "'", dbOpenSnapshot)
- 'companycell is required
- If Me.CompanyCell.Value = "" Then
- Beep
- Select Case MsgBox("all fields are required", vbOK)
- Case vbOK: 'do nothing
- Case Else: 'do nothing
- End Select
- Else
- Me.cellbox = Me.CompanyCell
- 'Evaluate values. if either of the combo boxes are empty then it will look up every possible combination and show it on the query 'Sales'
- If [Companyflux] = "" Or [CompanyRibbon] = "" Then
- MsgBox ("empty fields")
- DoCmd.OpenQuery "sales"
- DoCmd.OpenReport "sales", acViewReport
- 'if they aren't empty then it is going to se if the boxes match a record and open the query 'Sales'
- ElseIf Not rs1.EOF And Not rs2.EOF Then
- MsgBox ("finding records")
- DoCmd.OpenQuery "sales"
- DoCmd.OpenReport "sales", acViewReport
- 'If there is no data matching combination, say no match
- Else
- MsgBox ("no match")
- End If
- End If
- End Sub
Any help or references would be much appreciated.
Thank you