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

Question

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

Answer 1

Assuming all fields are on the currently open form, the VBA code is:

If IsNull(Me.Text140) Then

DoCmd.GoToControl "JobCustomerID"

End If

Answer 2

Hi Lawrence,

this keeps giving me a pop up saying invalid use of Null.

Answer 3

Try:

If Me.Text140 = 0 Then

Text140 may be a Numeric Data Type and/or may have its default value set to 0.

Answer 4

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

Answer 5

Hi Dirk,

if the Me.text is empty i think i want to pass it to the Me.JobCustomerID i think that they are both short text. The function works if i have either in the code but if the me.text140 column is empty is returns the above error.

Answer 6

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

Answer 7

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.

Answer 8

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