I keep getting an error when I try to format this number. I've done it in VBA before and I tried to change the SOPID to a variant, a string, and an integer.
Dim SOPID As Integer
SOPID = DMax("file_id", "tblSOP") + 1
'Output test data
MsgBox (format(SOPID, "000"))
I have no idea what I am doing wrong.
Assuming the code was pasted directly from your IDE, the casing of format is suspicious; that would be Format, unless there's a format variable or function that's in-scope, ...and that's being invoked here.
Look for a public (could be implicitly Public, or if it's in the same module then it could also be Private) format function procedure that expects an array argument: that's very likely what's being invoked here.
Rubberduck (free, open-source; I manage this project) can help you easily identify exactly what's being invoked and an inspection would tell you about any shadowed declarations, but to be sure you can fully-qualify the function call to avoid inadvertently invoking another function that's in scope:
MsgBox VBA.Strings.Format$(SOPID, "000")
Note that there are no parentheses around the argument list of a parameterized procedure call in VBA; the parentheses you have there are surrounding the first argument and making the expression be evaluated as a value that is then passed to the invoked function: this isn't necessary.
Also note the $: the VBA.Strings.Format function takes and returns a Variant; VBA.Strings.Format$ takes and returns a String. If you aren't dealing with any Null values (an Integer couldn't be Null), consider using the String-returning alias.
MsgBox format(SOPID, "000"). However, that's not supposed to cause this error afaikDMax("file_id", "tblSOP")return? Use adebug.printto find out before settingSOPIDformatand press Shift+F2 to see what is being called.