Posts by johnny001

Re: Combobox List Lost On Closing Workbook

Quote from Dave Hawley

No need to set the RowSource at run-time, just set it once at design-time.

Dave,

I'm struggling to do this, I don't have a userform, so where would I set this?

Thanks,

Re: Combobox List Lost On Closing Workbook

Something still not right, please see code below, which is generating a 438 run time error

Sub user_hist()
Dim rng As Range
Set rng = Sheets("tracking").Range("a2:a20")


ActiveSheet.Range("a" & ActiveSheet.Rows.Count).End(xlUp).Offset(1, 0).Value = Application.UserName & " " & Format$(Date, "mm-dd-yy") & " " & Time


With Sheets(1).ComboBox1
    .Style = fmStyleDropDownList
    .RowSource = rng.Address
End With
End Sub

Re: Combobox List Lost On Closing Workbook

Hi Dave,

Thanks for your suggestion. Populating the combobox from workbook_open event works for initial entries, but I would like to save entries generated while workbook is open. Do you have another way to do this?

Re: Combobox List Lost On Closing Workbook

Thanks for the quick reply. Is there a solution that doesn't involve storing the entries? The combobox values I'd like to keep are generated with a before_save event, and the entries are variable (username + timestamp)

Cheers,

Hello old freinds, Simple combobox query:

step 1. open excel, control toolbar, add new combobox (named combobox1)
step 2. create new module, with the following code:

sub test
with sheets(1).combobox1
    .AddItem "123"
end with
end sub

step 3. run macro "test", combobox1 should be populated, save and close file.
step 4. re-open file, drop down on combobox1, no values to select from.

What am i missing here?

Re: apostrophe error in macro [solved]

I am not sure if that's possbile since i'm calling the field, and not a specific record.

I solved this by using the following code:

Private Sub Supplier_Name_BeforeUpdate(Cancel As Integer)
On Error GoTo SuppName_Err
Dim sCriteria As String


sCriteria = "[invoiceno] = '" & [Forms]![Invoice Log Table 4]![InvoiceNo] & "'" & "And [Supplier Name] = " & """" & Me![Supplier Name] & """"


If Nz(DLookup("[invoiceno]", "[invoice log table]", sCriteria), 99) <> 99 Then
MsgBox "Duplicate Invoice Number. Please erase supplier and check your entry.", vbOKOnly, "Supplier/Invoice Number Validation"
Cancel = True
End If


SuppName_Exit:
   Exit Sub


SuppName_Err:
    MsgBox Error$
    Resume SuppName_Exit


End Sub

Re: apostrophe error in macro

I'm not sure what you mean....

replace in the underlying table? or in the code?

Cheers.

Johnny

Re: apostrophe error in macro

hey jva, cheers for the reply, i tried what u suggested, and moved it from a conditional macro to VBA.

i now have the following code:

DLookup("[invoiceno]", "[invoice log table]", "[invoiceno] = " & Chr$(34) & [Forms]![Invoice Log Table 4]![InvoiceNo] & Chr$(34) & " and [Supplier Name]= " & Chr$(34) & [Forms]![Invoice Log Table 4]![Supplier Name] & Chr$(34))

the code returns a blank supplier name if the supplier name has an apostrophe, and it crashes if the supplier name doesn't contain an apostrophe.... :confused:
any ideas?

Hello

I have a conditional macro that I use to check data entered on a form [invoice log table 4]. My checking looks at the supplier name [supplier name], and invoice number [invoiceno] fields, in a table [invoice log table], to see if the invoice already exists, and should thus not be entered again.
I'm doing this checking with the following bit of code as the condition

DLookUp("[InvoiceNO]","[Invoice log table]","[InvoiceNO] = '" & [Forms]![Invoice Log Table 4]![InvoiceNO] & "'" & " AND [Supplier Name]= '" & [Forms]![Invoice Log Table 4]![Supplier Name] & "'") Is Not Null

This code works fine until the supplier name has an apostrophe in it, at which point an syntax error message pops up. I've tried to use 4 double quotes ("""") in front of and after the supplier name, which worked for the VBA part, but it doesnt seem to work here.

Looking forward to your bright ideas and thanks in advance.

Re: SQL error

Thanks Shark,

I'm not exactly sure to what you mean, fairly new at access. Can you plz clarify where the Aggregate Function is changed?

Hello

I am trying to count the number of unique invoices using the following SQL in a query.

SELECT Count([InvoiceNo]) AS Uniques, [Invoice Log Table].[Batch Number]
FROM [Invoice Log Table] INNER JOIN [IFIS Receipt] ON [Invoice Log Table].ID = [IFIS Receipt].InvoiceID
WHERE ((([Invoice Log Table].[Batch Number]) Is Not Null));

I get the following error message:
You tried to execute a query that does not include the specified expression Batch Number as part of an aggregate function. (Error 3122)

Any help much appreciated

Re: conditional macro

Solved. Seems the code works. Sorry for any trouble.

Re: Find Record

Not too familiar w/ ADO, but still thinking about a possible solution. Will work on this more later. Cheers Carlmack.

Hi all,

I have a macro that I've copied straight off the microsoft website. The code checks the invoice number entered on a form to see if it already exists in the underlying table, thus to avoid duplication. The macro is called by the "before update" event of the invoice number. I have tried to modifty this code to look at 2 fields, that is the invoice number AND the supplier name. All fields are text fields.

Attached the first bit that works. This is placed under the "condition" column on the macro design screen, having MSGBOX as the action.

DLookUp("[InvoiceNO]","[Invoice log table]","[InvoiceNO] = Form.[InvoiceNO] ") Is Not Null

And i've tried to add the 2nd field but this is no longer doing any validation. I've also moved the macro to the supplier name "before update" event, as the invoice number gets entered first.

DLookUp("[InvoiceNO]","[Invoice log table]","[InvoiceNO] = '" & Forms![Invoice Log Table 4]![InvoiceNO]  & "'" & " AND [Supplier Name]= '" & Forms![Invoice Log Table 4]![Supplier Name] & "'") Is Not Null

Re: Find Record

Did the trick thanks a lot carlmack. Any idea on how to bypass showing blank records when it doesn't find a matching record?

Hello,

I have a form which allows a user to search for invoices by supplier/number or by username. Both searches are run from a command button, and the code can for both can be found below

Private Sub Find_Invoice_Click()


    Dim stDocName As String
    Dim stLinkCriteria As String


    stDocName = "Invoice Log Table 4"
    
    stLinkCriteria = "InvoiceNo = " & "'" & Me.InvoiceNo.Value & "'" & " And Supplier Name = " & "'" & Me.Supplier_Name.Value & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Find_Invoice_Click:
    Exit Sub
Err_Find_Invoice_Click:
    MsgBox Err.Description
    Resume Exit_Find_Invoice_Click
End Sub

this one has the following error message: Runtime error 3075, missing operator

Private Sub Command13_Click()
On Error GoTo Err_Command13_Click


    Dim stDocName As String
    Dim stLinkCriteria As String
    
    stDocName = "Invoice Log Table 4"


    stLinkCriteria = "Entered by = " & "'" & Me.Entered_by.Value & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_Command13_Click:
    Exit Sub
Err_Command13_Click:
    MsgBox Err.Description
    Resume Exit_Command13_Click
End Sub

this one has the "missing operator" error, but w/o the 3075 code. All values being searched are text.
Also, if no match is found, how can i put in a MSG BOX to state that?

Cheers for all ur help in advance.

Johnny

Re: auto populate list-box on form

hi guys

i have used this bit of code, and it sort of has the right effect:

Private Sub Send_To_AfterUpdate()
Me.Send_To.Requery
End Sub

The only problem is that i have to click the list box selection, after which i have to click it again to update.......

Is there a way to make it auto refresh?

Hi All

I have a form where the user enters a cost centre. Depending on the cost centre selected, I want the "approver" list box to get auto populated with the appropriate approver for that cost centre. There is a table in the db that has an approver for each cost centre.

If anyone can suggest a solution to having the approver populate the list box as soon as a cost centre is selected, I'll be vry thankful

Cheers

Johnny

Brandtrock,

In your attachment, you show the formulas used to re-format the data.

How did u get the info from columns D:F to look like the ones in G:I. Pasting values would yield differnet results.....did u manually erase records?

thx

johnny

In response to R Hind, the format of the data has not been created by me. it is a Lotus notes extract, and I cannot change the way it appears.
There are gaps, because that's how the Lotus database holds the records, which I cannot fix, and I'm sure the code would be quite messy. Also the 5 days column, can bea easily converted to a number, using morefunc, a quick and simple wmid can get the required results. I left it as is because i was hoping the answer would be provided via formulas

I looked at the revised pivot table and it is correct, however now i am not sure what to do with it since its not possible for me to reformat the 6000+ records.

So now my question again is, can a summary of each workstation be provided via formulas? I have tried to play around with filters and countifs to get the number of staff names between stations, but I have nothing concrete yet.
Are there any other ideas out there?