Asked by:
Using VBA to select a a field if the one selected is blank

-
Hi all,
i am trying to get access to select a different field if the one i have is blank, this is for an auto naming function going into excel, the code to go to excel is below:
'output filename
output_fname = "ARL" & Me.ARLRef & " - " & Me.AssayName & " - " & lookUpColumnValue(Application.CurrentDb, "Abv Cust Name", "Customers", "Id", Me.Text140) & ".xlsx"and what i want is if the Me.Text140 is blank then select the following field:
Me.JobCustomerID
i have tried iif statements but i cannot get it to work any help would be appreciated.
Regards,
Isaac
Question
All replies
-
-
-
-
Your question is a little unclear to me. You are invoking the (apparent) custom function "lookupColumnValue", passing it Me.Text140 among other arguments. If Me.Text140 is empty, do you want to pass Me.JobCustomerID to the function, or do you want to use Me.JobCustomerID in building output_fname instead of calling the function at all?
Are both Text140 and JobCustomerID the same type of control?
You may need to post the body of the function, "lookupColumnValue", of the error is occurring within that function.
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html -
-
From what you say, and if either works if passed to the function, then I think this might work:
output_fname = _ "ARL" & Me.ARLRef & " - " & Me.AssayName & " - " & _ lookUpColumnValue(Application.CurrentDb, "Abv Cust Name", "Customers", "Id", Nz(Me.Text140, Me.JobCustomerID)) & _ ".xlsx"
However, that does depend on everything being set up the way I think you're telling me, and I could be wrong.Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html -
Hi Dirk,
i must be missing something still trying to work out access, this database wasnt created by me so some of the setup seems a bit strange. not sure if this helps but both of the columns that this form is referencing is in the same table. i tried the above and still returns the invaild use of null error.
-
It's very hard to figure out without a copy of the database to work on. Can you post the database -- or a cut-down copy of the database, with just the elements necessary to demonstrate the problem -- on a file-sharing service like OneDrive or Dropbox? Then I could download it and see for myself what's going on.
I will be offline until tomorrow, but then I might be able to check it out.
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html