Mulitple updates in database

[M.Desmond]

I am using an excel database and have created a program to add and delete new
entries. Its a system of keeping track of Calf Vaccinations here at Cal
Poly's dairy unit. I am now attmepting to make some possible listbox form
that is populated by a due vaccinations pivot table that I can update all the
selected calves in the database at once that just recieved vaccinations that
day.
The problem is I am having a horrible time with writing VBA to "find" and
add new date to first empty row in "found" calf in the database.

Sorry if this sounds really confusing, any help you be greatly appreciated

 

[JLGWhiz]

Is your data configured so that you can search on a calf ID number and then
change the date in a different column? You neglected to describe your
database layout, so it is difficult to offer a solution.

 

[M.Desmond]

Thank you for responding, Yes my database is set-up so I can search by CAlf
ID but the dates of vaccinations are in different columns, separate yes

 

[JLGWhiz]

Let's assume your ID number is in column A and the Date of Vaccination is in
column B.

Sub changeDate()
Dim c As Range, lr As Long, searchRng As Range
lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Set searchRng = ActiveSheet.Range("A2:A" & lr)
Do
Set c = searchRng.Find(InputBox("Enter Calf ID". "Calf ID"). _
LookIn:=xlValues)
If c = False Or c = "" Then Exit Sub
If Not c Is Nothing Then
c.Offset(0, 1) = CDate(InputBox("Enter vaccination date for " &
c.Value))
If c.Offset(0, 1) = False Or c.Offset(0, 1) = "" Then Exit Sub

End If
more = MsgBox("Is there another vaccination to enter?, vbYesNo, "MORE?")
Loop While more = vbYes
End Sub

If the assumptions are incorrect the code will need to be modified to use
the correct columns. Otherwise, here is what will happen when you run the
code. It will ask you to enter a calf ID into an input box. If you enter an
ID, It will then ask you to enter a date of vaccination. If you enter a
date, it will ask you if there is another entry to be made. As long as you
answer yes, it will allow you to make entries. If you answer No, then it
ends. If you click cancel or leave the input area blank and click OK on
either of the first two questions, the procedure ends and you will have to
start over. I did not test this, so there could be bugs, but it is generally
the procedure you will need.

 

[M.Desmond]

Thank you again for your help,
So my excel programming is limited-Do I need to attach this code to a form,
module, sheet?
Because I am having trouble connecting this to my database.
maybe a tip for how to "create" this inputBox or message box?

Thank you again for your time,

mae

 

[JLGWhiz]

For this particular code to work, your data would have to be set up with the
dates of vaccination all listed in column B and the calf IDs would have to be
listed in column A. I don't know how they are actually listed because you
have never bothered to mention that in your postings. What I gave you was
based on the assumption that that was where the data was located. If the
data is not located there then the macro will fail. That being said, to use
the macro, copy it from the news reader to your clipboard, then in Excel,
press Alt + F11 to open the VB editor. If the large window of the editor is
dark, click Insert>Module, then paste the code into the code module window.
Then to run the macro, from Excel click Tools>Macro>Macros and click the
macro name to make it appear in the small window at the top of the dialog
box. Then click the Run button.

To test the macro, copy the columns from the current sheet to an unused
sheet and paste them in columns A and B as mentioned above. Then run the
macro. If it does what you want with the date and you can't figure out how
to apply it to your current worksheet, make a new posting in which you
describe your database layout and ask for assistance to apply the code.
Someone will help you.